Querying data: SELECT, joins and aggregation

Filter and order rows, choose the right join for the question you are asking, and group results without letting ONLY_FULL_GROUP_BY or a multiplied join quietly change the answer.

SELECT and filtering

The clauses are written in one order and evaluated in another. Read the logical order once and most surprising query behaviour stops being surprising.

SELECT   c.name, o.id, o.total
FROM     orders AS o
JOIN     customers AS c ON c.id = o.customer_id
WHERE    o.status = 'paid'
  AND    o.placed_at >= '2026-01-01'
  AND    c.country IN ('GB', 'DE')
GROUP BY c.name, o.id, o.total
HAVING   SUM(o.total) > 0
ORDER BY o.placed_at DESC
LIMIT    20 OFFSET 40;

Evaluation order is FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
WHERE filters rows before grouping; HAVING filters groups afterwards and therefore cannot use an index.
A SELECT alias is visible to ORDER BY but not to WHERE, because the alias does not exist yet when the rows are filtered.
LIMIT without ORDER BY returns an arbitrary set. Add a deterministic sort, ideally on a unique column.

The joins, and what each one returns

Join	Returns
`INNER JOIN`	Rows that match on both sides
`LEFT JOIN`	Every left row, with NULL on the right where there is no match
`RIGHT JOIN`	Mirror of the left join; rewriting it as a left join usually reads better
`CROSS JOIN`	The cartesian product, useful with a generated series
Anti-join	`LEFT JOIN ... WHERE right.id IS NULL` — left rows with no match
`UNION` / `UNION ALL`	Stacks result sets; `UNION` removes duplicates and therefore sorts

-- every customer, including those with no orders at all
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- moving the filter into the ON clause keeps the outer join intact
SELECT c.id, c.name, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id
      AND o.status = 'paid'
GROUP BY c.id, c.name;

Note the aggregate: COUNT(o.id) counts non-NULL values, while COUNT(*) counts rows, including the NULL-extended row the outer join produced. Using COUNT(*) reports one order for a customer who has none. Every aggregate function ignores NULLs except COUNT(*).

GROUP BY, HAVING and subqueries

SELECT status,
       COUNT(*)       AS orders,
       SUM(total)     AS revenue,
       AVG(total)     AS avg_order,
       MAX(placed_at) AS latest
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY status
HAVING SUM(total) > 10000
ORDER BY revenue DESC;

-- customers who ordered this year
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o
              WHERE o.customer_id = c.id
                AND o.placed_at >= '2026-01-01');

ONLY_FULL_GROUP_BY is enabled by default in MySQL 8: every selected column must be aggregated or named in GROUP BY. The mode exists because selecting an ungrouped column returns an arbitrary row from the group.
A condition on a plain column belongs in WHERE, where it can use an index; putting it in HAVING forces the work to happen after grouping.
EXISTS can stop at the first match. IN with a subquery must materialise the whole list, and NOT IN returns no rows at all when the list contains a single NULL.
GROUP BY ... WITH ROLLUP adds subtotal rows with NULL in the grouped columns — a quick way to get totals, as long as your readers know the difference between those NULLs and real missing data.

💡

Before trusting any aggregate over a join, run the same query without the join and compare row counts. A join that multiplies rows inflates SUM and COUNT without raising an error, and the result looks plausible.

FAQ

Why is my COUNT wrong?

Because a join multiplied the rows. If a parent has three matching children it appears three times, so counting rows counts matches rather than parents. Count DISTINCT ids, or aggregate in a subquery and join that result.

INNER or LEFT join?

Write the join that expresses the question. Use LEFT JOIN when a parent must survive with no children, and remember that a condition on the right-hand table placed in WHERE silently turns the outer join back into an inner join.

Data types, tables and constraints Indexes and reading EXPLAIN

Last refreshed 2026-09-18.

Querying data: SELECT, joins and aggregation

SELECT and filtering

The joins, and what each one returns

GROUP BY, HAVING and subqueries

FAQ

Related