---
layout: article
title: Querying rows
description: Read and write MySQL rows with SELECT, INSERT, UPDATE, and DELETE. Covers filtering, aggregation, pagination, and upserts.
---

Four statements do almost all the work in a relational database:

- **SELECT** reads rows
- **INSERT** adds new rows
- **UPDATE** changes existing rows
- **DELETE** removes rows

SQL is declarative: a query describes the result you want, and the database's optimizer decides how to produce it, choosing between indexes, scans, and join strategies on its own. That's why the same query keeps working as data grows and indexes change.

This page walks through each statement, plus the querying patterns you reach for daily: filtering, aggregation, pagination, and upserts.

# Setup

The examples below work against a small product catalog. Create and seed it first:

```sql
CREATE TABLE products (
    id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

INSERT INTO products (name, price, stock) VALUES
    ('Keyboard', 79.00, 120),
    ('Mouse', 29.50, 200),
    ('Monitor', 349.99, 14),
    ('Webcam', 59.00, 0),
    ('Desk mat', 19.90, 45);
```

# Read rows

A `SELECT` statement has three jobs, each handled by its own clause: choose which columns to return, decide which rows qualify, and put them in order:

```sql
SELECT name, price
FROM products
WHERE stock > 0
ORDER BY price DESC;
```

```text
name	price
Monitor	349.99
Keyboard	79.00
Mouse	29.50
Desk mat	19.90
```

Four of the five products come back: the Webcam is filtered out because its stock is 0, and the rest arrive sorted by price, highest first. The other columns still exist on those rows; the query just didn't ask for them. Without an `ORDER BY`, row order is arbitrary and can change between runs, so always order results the user will see.

`WHERE` accepts any boolean expression, built from a handful of operators:

- **Comparisons** (`=`, `<`, `>`, `<=`, `>=`, `<>`) work on numbers, text, and dates alike.
- **BETWEEN** matches a range, bounds included.
- **LIKE** matches string patterns, where `%` stands for any sequence of characters; under the default `utf8mb4` collation it compares case-insensitively, so `'%mo%'` matches both Mouse and Monitor.
- **IN** matches any value in a list.
- **AND** and **OR** combine conditions, with parentheses to group them.

```sql
SELECT name FROM products WHERE price BETWEEN 20 AND 100;   -- range
SELECT name FROM products WHERE name LIKE '%mo%';           -- pattern match
SELECT name FROM products WHERE stock IN (0, 14);           -- membership
SELECT name FROM products WHERE stock = 0 OR price < 25;    -- combined conditions
```

Avoid `SELECT *` in application code. Naming columns keeps results stable when the table gains columns later and avoids transferring data you don't use.

# Insert rows

`INSERT` names the columns it provides and gives a value for each; omitted columns fall back to their defaults, like the auto-generated `id` here:

```sql
INSERT INTO products (name, price, stock)
VALUES ('USB hub', 24.00, 80);

SELECT LAST_INSERT_ID();
```

`LAST_INSERT_ID()` returns the `AUTO_INCREMENT` value generated by the most recent insert on this connection. It is connection-scoped, so concurrent clients never see each other's IDs. Most drivers surface it directly on the statement result, no extra query needed.

# Update rows

`UPDATE` changes the columns you name on every row matching the `WHERE` clause:

```sql
UPDATE products
SET price = price * 0.90
WHERE stock > 100;
```

The `SET` expression can reference the row's current values, as the discount above does. Without a `WHERE` clause, `UPDATE` rewrites the entire table, so check the target set first with a `SELECT` using the same `WHERE` when running one by hand.

# Delete rows

`DELETE` removes every row matching the `WHERE` clause:

```sql
DELETE FROM products
WHERE stock = 0;
```

Without a `WHERE` clause, `DELETE FROM products` removes every row in the table. For hand-run maintenance, wrap the statement in a transaction so you can inspect the result and roll it back. See [Transactions](/docs/products/databases/mysql/concepts/transactions).

# Aggregate

An aggregate function computes a single value over a set of rows, such as a *count of orders* or a *total spend per customer*:

- **COUNT(*)** counts rows
- **SUM** and **AVG** total and average a numeric column
- **MIN** and **MAX** find the extremes

The demonstration needs a table where several rows belong to each customer:

```sql
CREATE TABLE orders (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(100) NOT NULL,
    total    DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (customer, total) VALUES
    ('ada', 120.00), ('ada', 80.00), ('grace', 45.50),
    ('grace', 30.00), ('grace', 25.00), ('linus', 999.99);
```

By default an aggregate covers every row in the table: `SELECT SUM(total) FROM orders` returns one number, the total across all six orders. `GROUP BY customer` changes that. The database first gathers rows with the same customer value together, Ada's two orders, Grace's three, Linus's one, then runs the aggregate functions once for each of those groups. The result has one row per customer instead of one row for the whole table:

```sql
SELECT customer,
       COUNT(*)   AS orders,
       SUM(total) AS lifetime_value
FROM orders
GROUP BY customer
HAVING SUM(total) > 100
ORDER BY lifetime_value DESC;
```

```text
customer	orders	lifetime_value
linus	1	999.99
ada	2	200.00
grace	3	100.50
```

Six order rows became three result rows, one per customer:

- Ada's two orders were summed into 200.00
- Grace's three orders were summed into 100.50
- Linus's single order stands alone at 999.99

In a grouped query, every selected column must be either grouped on (like `customer`) or aggregated (like `SUM(total)`), because each result row now represents many source rows.

`WHERE` and `HAVING` both filter, but at different stages:

- **WHERE** filters individual rows, before grouping. It cannot use aggregate results, because they haven't been computed yet.
- **HAVING** filters the groups, after the aggregates are computed.

Here `HAVING SUM(total) > 100` keeps only customers whose orders total more than 100. Grace passes at 100.50; a threshold of 150 would exclude her.

# Paginate

Pagination splits a large result into pages. There are two ways to do it:

- **Offset pagination** asks for "skip the first 20 rows, return the next 10". Simple, but the database still reads and discards every skipped row, so deep pages get slower and slower.
- **Keyset pagination** asks for "return the 10 rows after the last one I saw". The database jumps straight to that position, so every page costs the same as the first.

`LIMIT` and `OFFSET` are the offset approach:

```sql
SELECT id, name FROM products ORDER BY id LIMIT 2 OFFSET 2;
```

Keyset pagination filters on the last value seen instead:

```sql
SELECT id, name
FROM products
WHERE id > 2          -- last id from the previous page
ORDER BY id
LIMIT 2;
```

Keyset pagination stays fast at any depth because the index seeks straight to the boundary, provided the ordering columns are indexed. It requires a deterministic, unique ordering (a single column like `id`, or a composite) and can't jump to an arbitrary page number.

# Upsert

An upsert writes a row without knowing whether it already exists: insert it if it's new, update it if it's not. Stock counts, settings, and sync jobs all need this.

The obvious approach, `SELECT` to check and then `INSERT` or `UPDATE` accordingly, has a race condition: another connection can insert the same key between your check and your write, and your insert fails.

`INSERT ... ON DUPLICATE KEY UPDATE` performs the check and the write as one atomic statement, so the race cannot happen:

```sql
CREATE TABLE inventory (
    sku      VARCHAR(20) PRIMARY KEY,
    quantity INT NOT NULL
);

INSERT INTO inventory (sku, quantity) VALUES ('KB-01', 10);

-- Second write for the same key updates instead of failing
INSERT INTO inventory (sku, quantity) VALUES ('KB-01', 5) AS new
ON DUPLICATE KEY UPDATE quantity = inventory.quantity + new.quantity;

SELECT * FROM inventory;
```

```text
sku	quantity
KB-01	15
```

What happened:

- The first insert creates KB-01 with quantity 10.
- The second insert collides with it on the primary key, so instead of failing, it runs the `ON DUPLICATE KEY UPDATE` clause: existing 10 plus incoming 5 gives 15.
- The `AS new` alias names the row that would have been inserted, which is how the update reaches the incoming values.

To silently ignore duplicates instead, use `INSERT IGNORE`, but note it downgrades other errors to warnings too, so prefer `ON DUPLICATE KEY UPDATE` when in doubt.

# Subqueries and CTEs

A common table expression (CTE) names an intermediate result so a complex query reads top to bottom:

```sql
WITH big_spenders AS (
    SELECT customer
    FROM orders
    GROUP BY customer
    HAVING SUM(total) > 150
)
SELECT customer FROM big_spenders ORDER BY customer;
```

CTEs and subqueries are interchangeable in most positions; prefer whichever keeps the query readable. MySQL also supports `WITH RECURSIVE` for hierarchical data such as category trees.