Queries inside queries: scalar, correlated and derived-table subqueries, IN vs EXISTS (and the NOT IN NULL trap), CTEs and recursive CTEs, window functions for ranking, running totals and comparisons with neighbouring rows, top-N-per-group, and UNION/INTERSECT/EXCEPT.
12 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.
A subquery is a SELECT written inside another statement, in parentheses. The inner query produces a value, a list or a table, and the outer query uses it as if you'd typed that result in by hand. It lets you answer questions in two steps — "first work out the average salary, then find who earns more than that" — without running two queries from the application.
The mental model: evaluate the parentheses first, then substitute the result. What you can substitute depends on the shape the subquery returns:
SELECT name, salary
FROM employees
WHERE salary >(SELECTAVG(salary)FROM employees)ORDERBY salary DESC;
Result
+-------+-----------+
| name | salary |
+-------+-----------+
| Asha | 185000.00 |
| Ben | 142000.00 |
| Chloe | 142000.00 |
| Elena | 130000.00 |
| Dev | 118000.00 |
+-------+-----------+
5 rows in set (0.000 sec)
What’s happening
The subquery (SELECT AVG(salary) FROM employees) doesn't depend on the outer query, so MySQL evaluates it once: 116200.000000.
The outer query then behaves as if you'd written WHERE salary > 116200. It's a plain filter over the 10 rows.
Five people earn more: Asha (185000), Ben and Chloe (142000), Elena (130000) and Dev (118000).
Note you can't write WHERE salary > AVG(salary) directly — that's ERROR 1111 (HY000): Invalid use of group function, because WHERE runs before aggregation. The subquery is a separate, complete query with its own aggregation, so it's allowed.
AdvancedScalar subqueries: too many or no rows
Scalar subqueries must return at most one row
If a subquery used as a value returns more than one row, MySQL can't substitute it and stops with an error. If it returns no rows, it becomes NULL:
SQL
SELECT name, salary
FROM employees
WHERE salary >(SELECT salary FROM employees WHERE department_id =2);SELECT name,(SELECT name FROM departments WHERE id =99)AS no_such_department
FROM employees
WHERE id =1;
Result
ERROR 1242 (21000): Subquery returns more than 1 row
+------+--------------------+
| name | no_such_department |
+------+--------------------+
| Asha | NULL |
+------+--------------------+
1 row in set (0.000 sec)
What’s happening
Sales (department 2) has three employees, so the first subquery returns three salaries. salary > (three values) has no meaning: ERROR 1242 (21000): Subquery returns more than 1 row.
The fix depends on what you meant: > ALL (SELECT …) (more than every Sales salary), > ANY (…) (more than at least one), or > (SELECT MAX(salary) …).
The second query's subquery finds no department 99, so it returns zero rows. A scalar subquery with no rows evaluates to NULL — no error.
That silent NULL matters in WHERE: salary > (empty subquery) is salary > NULL, which is never true, so the whole query would return nothing.
AdvancedSubqueries in the SELECT list
A scalar subquery in the SELECT list computes one extra value per output row — here, how each employee compares to the company average:
SQL
SELECT name,
salary,
salary -(SELECTAVG(salary)FROM employees)AS vs_company_avg,(SELECTCOUNT(*)FROM employees)AS headcount
FROM employees
WHERE department_id =1;
A correlated subquery refers to a column of the outer query, so it can't be evaluated once up front: logically it runs again for every outer row, with that row's values plugged in. That's what lets it answer per-row questions like "does this employee earn more than their own department's average?"
Picture a teacher marking a stack of exam papers. For each paper (outer row) they look up the class average for that student's class (the inner query, using the student's class from the paper) and compare. Same lookup procedure, different input each time.
SQL
SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary >(SELECTAVG(x.salary)FROM employees x
WHERE x.department_id = e.department_id)ORDERBY e.department_id;
Result
+-------+---------------+-----------+
| name | department_id | salary |
+-------+---------------+-----------+
| Asha | 1 | 185000.00 |
| Elena | 2 | 130000.00 |
| Hiro | 3 | 88000.00 |
+-------+---------------+-----------+
3 rows in set (0.000 sec)
What’s happening
The inner query mentions e.department_id — a column of the outer row. That's the correlation: the subquery's answer depends on which employee is being looked at.
For Asha (department 1), the subquery computes Engineering's average, 146750; 185000 > 146750, so she's kept. For Ben (142000) it's the same average, and he's dropped.
For Elena (department 2) it computes Sales' average 108000 → kept. For Hiro (department 3), Support's 80000 → kept.
For Jonas, e.department_id is NULL, so the inner WHERE x.department_id = NULL matches nothing, AVG of nothing is NULL, and 91000 > NULL is unknown → dropped. People with no department silently vanish from results like this.
Result: Asha, Elena and Hiro — the top earner in each department, except that "top" here means "above average", which isn't the same thing in general.
AdvancedEXPLAIN: a dependent subquery
What the optimizer actually does
"Runs once per row" is the logical meaning, not necessarily the plan. EXPLAIN shows what MySQL 9.7 will do (it prints in tree format by default here):
SQL
EXPLAINSELECT e.name, e.salary
FROM employees e
WHERE e.salary >(SELECTAVG(x.salary)FROM employees x
WHERE x.department_id = e.department_id);
Result
-> Filter: (e.salary > (select #2)) (cost=1.25 rows=10)
-> Table scan on e (cost=1.25 rows=10)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: avg(x.salary) (cost=1.33 rows=1)
-> Index lookup on x using department_id (department_id = e.department_id) (cost=0.75 rows=2.5)
What’s happening
Table scan on e reads the outer table. For each row, Filter: (e.salary > (select #2)) evaluates the subquery.
Select #2 (subquery in condition; dependent) is the correlated subquery — "dependent" means it depends on the outer row, so it really is executed per row here.
Inside, Index lookup on x using department_id uses the index MySQL created for the foreign key, so each execution reads only that department's rows, not the whole table.
With 10 employees that's trivial. With 1 million employees and no index it would be 1 million scans of a million rows — the classic reason correlated subqueries get a bad name.
The rewrite that computes each department's average once is a join to a derived table (next) or a window function; MySQL sometimes does that rewrite itself (subquery "decorrelation"), and EXPLAIN is how you find out.
The same question as a join to pre-computed averages:
SQL
SELECT e.name, e.department_id, e.salary,ROUND(d.avg_salary)AS dept_avg
FROM employees e
JOIN(SELECT department_id,AVG(salary)AS avg_salary
FROM employees
GROUPBY department_id)AS d
ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary
ORDERBY e.department_id;
The derived table d groups once and produces one row per department with its average (four rows, including the NULL group).
The join matches each employee to their department's row. Jonas's NULL department doesn't equal the NULL group's key, so he drops out here too — = never matches NULL.
WHERE e.salary > d.avg_salary keeps the same three people. Bonus: dept_avg can now be shown, which the correlated version couldn't do without repeating the subquery.
Rule of thumb: correlated subqueries are perfect for EXISTS checks and readable one-offs; for per-group aggregates over big tables, compute the aggregates once and join.
AdvancedLatest order per customer
Latest row per parent
A common correlated pattern: each customer's most recent order.
SQL
SELECT c.name, o.id AS order_id, o.ordered_at
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_at =(SELECTMAX(o2.ordered_at)FROM orders o2
WHERE o2.customer_id = c.id)ORDERBY c.id;
For each (customer, order) pair, the subquery finds that customer's latest ordered_at.
Only the order whose timestamp equals that maximum survives. Rohit has orders on 2025-02-01 and 2025-03-15 → order 2 is kept.
Zara has no orders, so the inner join drops her before the subquery matters.
If a customer had two orders at exactly the same latest timestamp, both would appear — ties are kept. When you need exactly one row per group, use ROW_NUMBER() (see Top N per group).
?Predict the result
SQL
SELECT name
FROM employees e
WHERE salary =(SELECTMAX(salary)FROM employees x WHERE x.department_id = e.department_id)ORDERBY name;
Show answer
Asha, Elena and Hiro — but not Jonas.
SQL
SELECT name
FROM employees e
WHERE salary =(SELECTMAX(salary)FROM employees x WHERE x.department_id = e.department_id)ORDERBY name;
Result
+-------+
| name |
+-------+
| Asha |
| Elena |
| Hiro |
+-------+
3 rows in set (0.000 sec)
What’s happening
For each employee, the subquery returns the highest salary in that employee's department.
Engineering's max is 185000 (Asha), Sales' 130000 (Elena), Support's 88000 (Hiro). Each matches only its own top earner.
Jonas is the only person with no department and would be "top of his group" in a GROUP BY department_id, but here x.department_id = NULL matches nobody, the MAX is NULL, and 91000 = NULL isn't true.
To include him you'd need a NULL-safe comparison: x.department_id <=> e.department_id.
Both answer "is there a matching row over there?", but they ask it differently. IN compares a value to a list: customer_id IN (1, 2, 3), where the list comes from a subquery. EXISTS asks whether a subquery returns any row at all; it doesn't care what's selected, which is why people write SELECT 1. Both are semi-joins: they keep rows of the outer table that have at least one match, without duplicating them the way a join would.
For positive checks they're interchangeable and MySQL 8+ usually turns both into the same plan. The difference that bites is in the negative form: NOT IN returns nothing if the list contains a NULL, while NOT EXISTS does what you mean.
SQL
SELECT name FROM customers
WHERE id IN(SELECT customer_id FROM orders)ORDERBY id;SELECT name FROM customers c
WHEREEXISTS(SELECT1FROM orders o WHERE o.customer_id = c.id)ORDERBY id;
Result
+-------+
| name |
+-------+
| Rohit |
| Maya |
| Liam |
| Sofia |
| Noah |
+-------+
5 rows in set (0.001 sec)
+-------+
| name |
+-------+
| Rohit |
| Maya |
| Liam |
| Sofia |
| Noah |
+-------+
5 rows in set (0.000 sec)
What’s happening
The IN subquery returns the customer_id of every order: 1, 1, 2, 3, 3, 4, 5, 2. Duplicates don't matter to IN.
Each customer is kept if their id is in that list: Rohit, Maya, Liam, Sofia and Noah. Rohit has two orders but appears once — unlike JOIN orders, which would list him twice.
The EXISTS version is correlated: for each customer it asks "is there any order with customer_id = c.id?" and stops at the first one it finds.
Same five names. Zara is missing from both because she has no orders.
AdvancedThe NOT IN NULL trap
"Employees who aren't anybody's manager" — the obvious query returns nothing:
SQL
SELECT name FROM employees
WHERE id NOTIN(SELECT manager_id FROM employees);SELECT name FROM employees e
WHERENOTEXISTS(SELECT1FROM employees r WHERE r.manager_id = e.id);
Result
Empty set (0.000 sec)
+-------+
| name |
+-------+
| Chloe |
| Dev |
| Farid |
| Grace |
| Isla |
| Jonas |
+-------+
6 rows in set (0.000 sec)
What’s happening
The subquery SELECT manager_id FROM employees returns NULL, 1, 1, 2, 1, 5, 5, 1, 8, 1 — Asha has no manager, so one value is NULL.
id NOT IN (NULL, 1, 2, 5, 8, …) means id <> NULL AND id <> 1 AND id <> 2 …. For Chloe (id 3), every comparison with a number is true, but 3 <> NULL is unknown, and true AND unknown is unknown. WHERE only keeps rows that are true, so Chloe is dropped — and so is everyone else.
The first query returns Empty set, with no error and no warning.
NOT EXISTS never compares with the NULL: for Chloe it asks "is there a row whose manager_id = 3?" — no — so she's kept. It returns the six non-managers: Chloe, Dev, Farid, Grace, Isla and Jonas.
If you prefer NOT IN, exclude the nulls yourself: WHERE id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL).
AdvancedEXPLAIN: NOT EXISTS as an antijoin
What MySQL does with them
Since MySQL 8.0.17, NOT EXISTS and NOT IN subqueries can become antijoins (and since 8.0.16 EXISTS gets the same semijoin treatment as IN). Here's the plan for the NOT EXISTS query:
SQL
EXPLAINSELECT name FROM employees e
WHERENOTEXISTS(SELECT1FROM employees r WHERE r.manager_id = e.id);
Result
-> Nested loop antijoin (cost=12.2 rows=100)
-> Table scan on e (cost=1.25 rows=10)
-> Single-row index lookup on <subquery2> using <auto_distinct_key> (manager_id = e.id) (cost=3.66..3.66 rows=1)
-> Materialize with deduplication (cost=3.55..3.55 rows=10)
-> Filter: (r.manager_id is not null) (cost=1.25 rows=10)
-> Covering index scan on r using manager_id (cost=1.25 rows=10)
What’s happening
Nested loop antijoin is the antijoin: for each outer row, look for a match, and keep the row only if there is none.
MySQL first builds a deduplicated list of manager ids (Materialize with deduplication over a Covering index scan on r using manager_id), filtering out NULLs (r.manager_id is not null).
Then for every employee e it does a Single-row index lookup in that temporary list. One lookup per employee, not one subquery execution per employee.
So the old advice "EXISTS is faster than IN" (true in MySQL 5.5 and earlier, where IN (subquery) was often run as a dependent subquery) is mostly obsolete. Choose by semantics: NOT EXISTS for "no matching row", because of the NULL trap.
?Spot the bug
"Products that have never been ordered":
SQL
SELECT name FROM products
WHERE id NOTIN(SELECT product_id FROM order_items);
Does this suffer from the NULL trap?
Show answer
No — it correctly returns the iPhone 17. order_items.product_id is NOT NULL (it's part of the primary key), so the list can never contain a NULL.
SQL
SELECT name FROM products
WHERE id NOTIN(SELECT product_id FROM order_items);
Result
+-----------+
| name |
+-----------+
| iPhone 17 |
+-----------+
1 row in set (0.000 sec)
What’s happening
The subquery lists every ordered product id; product 4 (iPhone 17) never appears.
Because product_id is declared NOT NULL, every id <> product_id comparison is definitely true or false, so NOT IN behaves.
The trap only appears when the subquery column is nullable — like employees.manager_id, or any column that comes from the optional side of a LEFT JOIN.
The habit to build: reach for NOT EXISTS and you never have to check.
A derived table is a subquery in the FROM clause. Its result is a temporary, nameless table that the outer query reads like any other — so you can aggregate in two levels ("average of per-customer totals"), filter on a computed column, or join pre-aggregated data. It must have an alias, because every table in FROM needs a name.
Think of it as a scratch table you build in the middle of the query and throw away at the end. MySQL may actually materialise it as a temporary table, or it may merge it into the outer query so it never exists — the result is the same either way.
SQL
SELECTROUND(AVG(total),2)AS avg_customer_spend,MAX(total)AS best_customer_spend
FROM(SELECT o.customer_id,SUM(i.quantity * i.unit_price)AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY o.customer_id)AS per_customer;
Result
+--------------------+---------------------+
| avg_customer_spend | best_customer_spend |
+--------------------+---------------------+
| 720.20 | 1164.00 |
+--------------------+---------------------+
1 row in set (0.000 sec)
What’s happening
The inner query runs first and produces one row per customer with their total spend on non-cancelled orders: Rohit 957 + 88 = 1045, Maya 1048 + 116 = 1164, Liam 127, Sofia 249, Noah 1016.
AS per_customer names that five-row result; the outer query treats it as a table with columns customer_id and total.
The outer AVG(total) averages the five totals: 3601 / 5 = 720.2. MAX(total) is Maya's 1164.00.
You can't nest aggregates directly — AVG(SUM(…)) is ERROR 1111 (HY000): Invalid use of group function — so a two-level aggregation always needs a derived table, CTE or window function.
AdvancedThe alias is mandatory
Forget the alias and MySQL refuses the query:
SQL
SELECT*FROM(SELECT1AS x);SELECT*FROM(SELECT1,2)AS t (a, b);
Result
ERROR 1248 (42000): Every derived table must have its own alias
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.000 sec)
What’s happening
The first statement fails with ERROR 1248 (42000): Every derived table must have its own alias.
The second gives the derived table a name (t) and also renames its columns with a column list (a, b) — handy when the inner select's expressions have no good names.
PostgreSQL 16 and later made the alias optional; MySQL 9.7 still requires it, so always write one.
AdvancedLATERAL derived tables (8.0.14+)
LATERAL: derived tables that see the outer row
A normal derived table is computed independently and can't refer to tables listed before it in FROM. Since MySQL 8.0.14, LATERAL lifts that restriction: the derived table is re-evaluated for each row of the preceding table, like a correlated subquery that can return several rows and columns. Each customer's two most expensive order lines:
SQL
SELECT c.name,top.product,top.line_total
FROM customers c
JOIN LATERAL (SELECT p.name AS product, i.quantity * i.unit_price AS line_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.customer_id = c.id
ORDERBY line_total DESCLIMIT2)AStopONTRUEORDERBY c.id,top.line_total DESC;
A common table expression (CTE) is a named subquery written before the main query in a WITH clause. Instead of nesting a derived table inside the FROM, you give it a name at the top and then use that name as if it were a table. MySQL added CTEs in 8.0 (the 8.0.1 milestone); MySQL 5.7 doesn't have them, which is why old codebases are full of nested derived tables.
The mental model is variables for queries: WITH totals AS (…) is like const totals = … at the top of a function. You read the query top to bottom as a series of named steps, each building on the previous ones — instead of inside out.
SQL
WITH per_customer AS(SELECT o.customer_id,SUM(i.quantity * i.unit_price)AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY o.customer_id
),
overall AS(SELECTAVG(total)AS avg_total FROM per_customer
)SELECT c.name, p.total,ROUND(o.avg_total,2)AS avg_total
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
CROSSJOIN overall o
WHERE p.total > o.avg_total
ORDERBY p.total DESC;
Result
+-------+---------+-----------+
| name | total | avg_total |
+-------+---------+-----------+
| Maya | 1164.00 | 720.20 |
| Rohit | 1045.00 | 720.20 |
| Noah | 1016.00 | 720.20 |
+-------+---------+-----------+
3 rows in set (0.000 sec)
What’s happening
per_customer is defined first: each customer's spend on non-cancelled orders (the same five totals as in the derived-table example).
overall is a second CTE that reads the first one — CTEs listed later in the same WITH can use earlier ones. It produces one row: avg_total = 720.2.
The main query joins per_customer to customers for names, and CROSS JOIN overall attaches the single average row to every customer row.
WHERE p.total > o.avg_total keeps customers above average: Maya (1164), Rohit (1045) and Noah (1016).
Note per_customer is referenced twice (inside overall and in the main query). With derived tables you'd have had to write that subquery out twice.
AdvancedCTEs in UPDATE and DELETE
A WITH clause can also start an UPDATE or DELETE in MySQL, which makes multi-step changes readable. Here we give a 5% raise to everyone below their department's average, then check (and roll back, so the sample data stays intact):
SQL
STARTTRANSACTION;WITH dept_avg AS(SELECT department_id,AVG(salary)AS avg_salary
FROM employees
GROUPBY department_id
)UPDATE employees e
JOIN dept_avg d ON d.department_id = e.department_id
SET e.salary = e.salary *1.05WHERE e.salary < d.avg_salary;SELECT name, salary FROM employees WHERE id IN(2,3,4,6,7,9);ROLLBACK;
dept_avg computes each department's average before anything changes: 146750, 108000, 80000.
The UPDATE joins every employee to their department's average and raises those below it: Ben, Chloe and Dev in Engineering, Farid and Grace in Sales, Isla in Support — 6 rows, matching the Rows matched: 6 Changed: 6 line.
Dev goes 118000 → 123900, Isla 72000 → 75600. The averages don't move mid-statement, because the CTE is evaluated as a snapshot for the statement.
ROLLBACK undoes the raise, so later examples still see the original salaries.
A recursive CTE refers to itself. It's how SQL walks hierarchies (org charts, category trees, threaded comments) and generates sequences (every date in a range), neither of which a fixed number of joins can do.
It always has two parts joined by UNION ALL:
the anchor (non-recursive part): the starting rows, e.g. the CEO;
the recursive part: a query that joins the CTE to the base table to produce the next level from the rows of the previous level.
Think of it as a loop: start with the anchor rows; feed them into the recursive part to get the next batch; feed that batch in; stop when a batch comes back empty. The CTE's result is all batches stacked together.
SQL
WITH RECURSIVE org AS(SELECT id, name, manager_id,0AS depth,
CAST(name ASCHAR(200))ASchainFROM employees
WHERE manager_id ISNULLUNIONALLSELECT e.id, e.name, e.manager_id, org.depth +1,
CONCAT(org.chain,' > ', e.name)FROM employees e
JOIN org ON e.manager_id = org.id
)SELECT CONCAT(REPEAT(' ', depth), name)AS org_chart, depth,chainFROM org
ORDERBYchain;
Result
+---------------+-------+----------------------+
| org_chart | depth | chain |
+---------------+-------+----------------------+
| Asha | 0 | Asha |
| Ben | 1 | Asha > Ben |
| Dev | 2 | Asha > Ben > Dev |
| Chloe | 1 | Asha > Chloe |
| Elena | 1 | Asha > Elena |
| Farid | 2 | Asha > Elena > Farid |
| Grace | 2 | Asha > Elena > Grace |
| Hiro | 1 | Asha > Hiro |
| Isla | 2 | Asha > Hiro > Isla |
| Jonas | 1 | Asha > Jonas |
+---------------+-------+----------------------+
10 rows in set (0.000 sec)
What’s happening
Anchor:WHERE manager_id IS NULL finds Asha. Batch 0 is one row: depth 0, chain Asha.
Iteration 1: the recursive part joins employees to the rows of batch 0 on e.manager_id = org.id, finding everyone whose manager is Asha: Ben, Chloe, Elena, Hiro and Jonas, at depth 1, chain Asha > Ben and so on.
Iteration 2: joins to batch 1's rows: Dev (manager Ben), Farid and Grace (Elena), Isla (Hiro), at depth 2.
Iteration 3: nobody reports to Dev, Farid, Grace or Isla, so the batch is empty and the recursion stops. All 10 employees were reached.
CAST(name AS CHAR(200)) in the anchor matters: the column types of a recursive CTE come from the anchor only. Without the cast, chain would be as wide as name and the longer chains wouldn't fit (see below).
ORDER BY chain sorts the paths alphabetically, which conveniently puts each person right under their manager; REPEAT(' ', depth) indents them into a tree.
AdvancedGenerating months to fill report gaps
Generating a series: filling gaps in a report
Revenue per month is easy with GROUP BY — but months with no orders don't appear at all, and a chart would skip them. A recursive CTE can generate every month, and a LEFT JOIN fills the holes with zero:
SQL
WITH RECURSIVE months AS(SELECTDATE('2025-01-01')AS month_start
UNIONALLSELECT month_start +INTERVAL1MONTHFROM months
WHERE month_start <'2025-04-01'),
revenue AS(SELECT DATE_FORMAT(o.ordered_at,'%Y-%m-01')AS month_start,SUM(i.quantity * i.unit_price)AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY month_start
)SELECT DATE_FORMAT(m.month_start,'%Y-%m')ASmonth,COALESCE(r.revenue,0)AS revenue
FROM months m
LEFTJOIN revenue r ON r.month_start = m.month_start
ORDERBY m.month_start;
The anchor produces 2025-01-01. Each iteration adds a month while the previous value is before April: 2025-02-01, 2025-03-01, 2025-04-01. Then WHERE month_start < '2025-04-01' is false for April's row and the recursion stops. Four rows.
revenue is an ordinary CTE: revenue per month for months that have orders (February to April). January isn't there at all.
months LEFT JOIN revenue keeps all four generated months. January has no match, so r.revenue is NULL and COALESCE turns it into 0.
February is 957 + 1048 = 2005.00, March 127 + 88 + 249 = 464.00, April 1016 + 116 = 1132.00.
The same trick generates days, hours, or numbers 1..N. PostgreSQL has generate_series() for this; MySQL needs the recursive CTE.
AdvancedRunaway recursion and type truncation
Two errors you'll meet. A recursive part with no stopping condition, and a string column that grows beyond its anchor width:
SQL
WITH RECURSIVE n AS(SELECT1AS i
UNIONALLSELECT i +1FROM n
)SELECTCOUNT(*)FROM n;WITH RECURSIVE cte AS(SELECT1AS n,'abc'AS str
UNIONALLSELECT n +1, CONCAT(str, str)FROM cte WHERE n <3)SELECT*FROM cte;
Result
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
ERROR 1406 (22001): Data too long for column 'str' at row 1
What’s happening
The first CTE never stops producing rows (there's no WHERE in the recursive part). MySQL aborts it once the depth passes cte_max_recursion_depth (default 1000): ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
That limit is a safety net, not a feature: if you really need deeper recursion (say, 5000 days), raise it for the session with SET SESSION cte_max_recursion_depth = 10000.
In the second CTE, the anchor's 'abc' makes str a 3-character column. The first recursive step tries to store 'abcabc': in strict mode that's ERROR 1406 (22001): Data too long for column 'str' at row 1 (in non-strict mode it would silently truncate back to 'abc').
The fix is to size the column in the anchor: CAST('abc' AS CHAR(20)) AS str — exactly why the org-chart query cast name to CHAR(200).
A window function computes something across a set of rows related to the current row — but unlike GROUP BY, it doesn't collapse the rows. Every input row stays in the output, and gets an extra column calculated over its "window". MySQL added them in 8.0.2; they're the single biggest reason SQL interview questions got easier to answer.
The mental model: GROUP BY folds the rows into one per group; OVER leaves every row in place and staples the group's figures onto each one.OVER () means "the window is the whole result". OVER (PARTITION BY department_id) means "the window is all rows with the same department as this row". ORDER BY inside OVER adds an order within each window, which is what ranking and running totals need.
All 10 employees come out — no grouping happened. Each row gets extra columns computed over its window.
AVG(salary) OVER (PARTITION BY department_id): for Asha, the window is the four Engineering rows, so dept_avg = 146750; for Elena, the three Sales rows → 108000. Jonas's partition is the NULL department (window functions, like GROUP BY, put all NULLs together), so his dept_avg is his own salary.
COUNT(*) OVER (…) gives each row its department's headcount; SUM(salary) OVER (…) its department's payroll, so pct_of_dept is each person's share: Asha 185000 / 587000 = 31.5%.
SUM(salary) OVER () has an empty window spec: the window is every row, so company_payroll is 1162000.00 on all rows.
Doing this with GROUP BY would need a derived table of per-department aggregates joined back to employees. The window version reads the table once and keeps the detail.
AdvancedWhere window functions are allowed
Window functions are computed afterWHERE, GROUP BY and HAVING — late in the logical order, just before ORDER BY. So they're only allowed in the SELECT list and ORDER BY:
SQL
SELECT name, salary
FROM employees
WHERE salary >AVG(salary)OVER();
Result
ERROR 3593 (HY000): You cannot use the window function 'avg' in this context.'
What’s happening
WHERE decides which rows exist; the window would be computed over those rows. Using the window inside WHERE is circular.
MySQL rejects it: ERROR 3593 (HY000): You cannot use the window function 'avg' in this context.
The fix is to compute the window in a CTE or derived table and filter outside it — which is exactly the shape of every Top N per group query.
AdvancedWindows over grouped rows
Window functions over grouped rows
Because windows run after GROUP BY, you can window over the groups themselves — each department's payroll and its share of the company total:
GROUP BY department_id first reduces the 10 rows to four group rows, each with SUM(salary).
SUM(SUM(salary)) OVER () is a window SUM over those four group rows: 587000 + 324000 + 160000 + 91000 = 1162000. The inner SUM is the aggregate, the outer one is the window function.
Engineering's share is 587000 / 1162000 = 50.5%.
Nested aggregates are an error (AVG(SUM(x))), but an aggregate inside a window function is fine, because they run at different stages.
AdvancedNamed windows with WINDOW
Named windows
When several columns use the same window, name it once with a WINDOW clause:
SQL
SELECT name, salary,MIN(salary)OVER w AS dept_min,MAX(salary)OVER w AS dept_max
FROM employees
WHERE department_id =2
WINDOW w AS(PARTITIONBY department_id);
These three number the rows of each window in the order given by ORDER BY inside OVER. They agree until there's a tie, and the tie is the whole point of the interview question:
ROW_NUMBER() — 1, 2, 3, 4: every row gets a different number, ties broken arbitrarily.
RANK() — 1, 2, 2, 4: tied rows share a rank, and the next rank skips ("Olympic" ranking: two silvers, no bronze).
DENSE_RANK() — 1, 2, 2, 3: tied rows share a rank, and the next rank doesn't skip.
The sample data has exactly such a tie: Ben and Chloe both earn 142000.00 in Engineering.
SQL
SELECT name,
salary,
ROW_NUMBER()OVER w AS row_num,
RANK()OVER w AS rnk,
DENSE_RANK()OVER w AS dense_rnk
FROM employees
WHERE department_id =1
WINDOW w AS(ORDERBY salary DESC);
The window is the four Engineering rows ordered by salary, highest first: Asha 185000, then Ben and Chloe at 142000, then Dev at 118000.
ROW_NUMBER() simply counts: Asha 1, Ben 2, Chloe 3, Dev 4. Which of Ben and Chloe gets 2 is not defined by the query — they're equal under ORDER BY salary DESC, so a different plan could swap them.
RANK() gives tied rows the same rank: Ben and Chloe are both 2. Dev gets 4, because two rows are ahead of him at rank 2 — rank = 1 + the number of rows strictly ahead.
DENSE_RANK() also gives Ben and Chloe 2, but Dev gets 3: it counts distinct values ahead of him (185000 and 142000), not rows.
Same input, three different answers for Dev. Ask yourself which question you're answering: "the 3rd row" (ROW_NUMBER), "the competition placing" (RANK) or "the 3rd-highest salary level" (DENSE_RANK).
AdvancedCompany-wide and per-department ranks
Company-wide and per department
Add PARTITION BY and the numbering restarts in each partition:
company_rank ranks all 10 salaries together. Ben and Chloe share 2, and Elena, the next salary level (130000), is 3.
dept_rank restarts at 1 in every department: Asha, Elena, Hiro and Jonas are each 1 in their own partition.
Dev is 4 company-wide (four distinct salary levels at or above his: 185000, 142000, 130000, 118000) but 3 inside Engineering.
The final ORDER BY company_rank, name is the query's own ordering, independent of the windows' orderings; it's also what makes Ben appear before Chloe in the output.
AdvancedNth highest salary
"Find the 3rd-highest salary" is a classic. Which ranking function you choose changes the answer:
SQL
WITH ranked AS(SELECT name, salary,
RANK()OVER(ORDERBY salary DESC)AS rnk,
DENSE_RANK()OVER(ORDERBY salary DESC)AS dense_rnk
FROM employees
)SELECT'RANK = 3'ASrule, name, salary FROM ranked WHERE rnk =3UNIONALLSELECT'DENSE_RANK = 3', name, salary FROM ranked WHERE dense_rnk =3;SELECTDISTINCT salary
FROM employees
ORDERBY salary DESCLIMIT1OFFSET2;
Result
+----------------+-------+-----------+
| rule | name | salary |
+----------------+-------+-----------+
| DENSE_RANK = 3 | Elena | 130000.00 |
+----------------+-------+-----------+
1 row in set (0.000 sec)
+-----------+
| salary |
+-----------+
| 130000.00 |
+-----------+
1 row in set (0.000 sec)
What’s happening
Company-wide, RANK() gives Asha 1, Ben and Chloe 2, and Elena 4 (three rows are ahead of her). No row has rank 3, so the RANK = 3 branch returns nothing.
DENSE_RANK() gives Elena 3, because 130000 is the third distinct salary. The DENSE_RANK = 3 branch returns Elena, 130000.00.
The second query is the pre-window-function answer: DISTINCT collapses Ben's and Chloe's salaries into one value, ORDER BY … DESC sorts, and LIMIT 1 OFFSET 2 skips two values to take the third: 130000.00.
"3rd-highest salary" almost always means the third distinct value, so DENSE_RANK (or DISTINCT … LIMIT) is the right tool. ROW_NUMBER() = 3 would return Chloe or Ben — the tie makes it a coin flip.
AdvancedMake ROW_NUMBER deterministic
When you use ROW_NUMBER() to pick exactly one row (pagination, de-duplication, "latest per group"), add a tie-breaker that's unique, usually the primary key:
SQL
SELECT name, salary,
ROW_NUMBER()OVER(ORDERBY salary DESC, id)AS row_num
FROM employees
WHERE department_id =1;
Result
+-------+-----------+---------+
| name | salary | row_num |
+-------+-----------+---------+
| Asha | 185000.00 | 1 |
| Ben | 142000.00 | 2 |
| Chloe | 142000.00 | 3 |
| Dev | 118000.00 | 4 |
+-------+-----------+---------+
4 rows in set (0.000 sec)
What’s happening
ORDER BY salary DESC, id sorts by salary and, among equal salaries, by id.
Ben (id 2) now always gets 2 and Chloe (id 3) always gets 3, on every run and every plan.
Without the tie-breaker the numbers are still unique, but which tied row gets which number can change between executions, which shows up as rows "jumping" between pages or a different duplicate being deleted.
AdvancedNTILE, PERCENT_RANK and CUME_DIST
The other ranking functions
NTILE(n) splits the ordered rows into n roughly equal buckets; PERCENT_RANK() and CUME_DIST() give relative positions between 0 and 1:
SQL
SELECT name, salary,
NTILE(3)OVER w AS third,ROUND(PERCENT_RANK()OVER w,2)AS pct_rank,ROUND(CUME_DIST()OVER w,2)AS cumulative
FROM employees
WINDOW w AS(ORDERBY salary DESC, id);
NTILE(3) deals 10 rows into 3 buckets: sizes 4, 3, 3 (the earlier buckets get the extra row). Asha, Ben, Chloe and Elena are in bucket 1.
PERCENT_RANK() is (rank - 1) / (rows - 1): Asha 0, Isla 1. Each step is 1/9 ≈ 0.11.
CUME_DIST() is "fraction of rows at or before this one": Asha 0.1, Isla 1.
Because the window orders by salary DESC, id, Ben and Chloe are no longer peers here, so they get different values. With ORDER BY salary DESC alone, both would get the same PERCENT_RANK and CUME_DIST.
?Predict the result
Sales salaries are Elena 130000, Grace 99000, Farid 95000. What does this return?
SQL
SELECT name,
RANK()OVER(ORDERBY department_id)AS r,
DENSE_RANK()OVER(ORDERBY department_id)AS dr,
ROW_NUMBER()OVER(ORDERBY department_id, id)AS rn
FROM employees
WHERE department_id IN(2,3)ORDERBY rn;
Show answer
Everyone in Sales ties on department_id, and so does everyone in Support.
SQL
SELECT name,
RANK()OVER(ORDERBY department_id)AS r,
DENSE_RANK()OVER(ORDERBY department_id)AS dr,
ROW_NUMBER()OVER(ORDERBY department_id, id)AS rn
FROM employees
WHERE department_id IN(2,3)ORDERBY rn;
The window orders by department_id only, so the three Sales rows are all peers (value 2) and the two Support rows are peers (value 3). Salary plays no part.
RANK(): the Sales rows are all 1; Support starts at 4, because three rows come before it.
DENSE_RANK(): Sales 1, Support 2 — one step per distinct value.
ROW_NUMBER() with the id tie-breaker numbers them 1 to 5 in id order: Elena, Farid, Grace, Hiro, Isla.
The lesson: ranks are about the ORDER BY inside OVER, not about whatever column you happen to be thinking of.
Many questions compare a row with its neighbours: "how much did revenue change since the previous order?", "how many days between a customer's orders?", "what's the total so far?". Before window functions this needed self-joins on "the previous row", which is awkward to express. Window functions make it direct:
LAG(expr, n, default) reads expr from the row n rows before the current one in the window's order (default n = 1).
LEAD(expr, n, default) reads from the row n rows after.
SUM(expr) OVER (ORDER BY …) is a running total: the sum of all rows up to the current one.
Think of the rows as a queue of people in order. LAG lets each person look over their shoulder at the one behind; LEAD look at the one in front; the running SUM is the tally the queue has accumulated by the time it reaches you.
SQL
WITH order_totals AS(SELECT o.id,DATE(o.ordered_at)AS ordered_on,SUM(i.quantity * i.unit_price)AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY o.id, ordered_on
)SELECT id, ordered_on, total,
LAG(total)OVER(ORDERBY ordered_on)AS prev_total,
total - LAG(total)OVER(ORDERBY ordered_on)AS change_vs_prev,
LEAD(ordered_on)OVER(ORDERBY ordered_on)AS next_order_on,SUM(total)OVER(ORDERBY ordered_on)AS running_total
FROM order_totals
ORDERBY ordered_on;
The CTE produces one row per non-cancelled order with its date and total (seven orders). The window functions then all order those rows by date.
LAG(total) on the first row (order 1, 2025-02-01) has no previous row, so it's NULL — and so is change_vs_prev. On order 3, LAG(total) is order 1's 957.00, and the change is 1048 - 957 = +91.00.
LEAD(ordered_on) looks forward: order 1's next order is on 2025-02-10; the last row (order 8) has no next row → NULL.
SUM(total) OVER (ORDER BY ordered_on) adds up everything from the first row to the current one: 957 → 2005 → 2132 → … → 3601.00. The last value equals the grand total, as a running total should.
Why does ORDER BY inside OVER turn SUM into a running sum? Because adding ORDER BY changes the default frame to "from the start of the partition up to the current row" — see Window frames.
AdvancedDays between a customer's orders
Per-partition comparisons: days between orders
PARTITION BY restarts the neighbour logic for each group — here, each customer's gap between consecutive orders:
SQL
SELECT c.name, o.id AS order_id,DATE(o.ordered_at)AS ordered_on,
LAG(DATE(o.ordered_at))OVER w AS previous_order_on,
DATEDIFF(o.ordered_at, LAG(o.ordered_at)OVER w)AS days_since_previous,
ROW_NUMBER()OVER w AS nth_order
FROM orders o
JOIN customers c ON c.id = o.customer_id
WINDOW w AS(PARTITIONBY o.customer_id ORDERBY o.ordered_at)ORDERBY c.id, o.ordered_at;
PARTITION BY o.customer_id gives every customer their own window, ordered by date.
Each customer's first order has no previous one: previous_order_on and days_since_previous are NULL, and nth_order is 1.
Rohit's second order (2025-03-15) looks back to 2025-02-01: DATEDIFF gives 42 days. Maya's gap (2025-02-10 to 2025-04-05) is 54 days; Liam's (2025-02-14 to 2025-03-01) is 15.
LAG never crosses into another customer's rows — the partition is a wall. Without PARTITION BY, Maya's first order would be compared with Rohit's last.
AdvancedMonth-over-month growth with LAG
Default values and month-over-month growth
LAG's third argument replaces the NULL on the first row. Combined with GROUP BY, it gives month-over-month growth in one query:
SQL
SELECT DATE_FORMAT(o.ordered_at,'%Y-%m')ASmonth,SUM(i.quantity * i.unit_price)AS revenue,
LAG(SUM(i.quantity * i.unit_price),1,0)OVER(ORDERBY DATE_FORMAT(o.ordered_at,'%Y-%m'))AS prev_month,ROUND(100*(SUM(i.quantity * i.unit_price)/ LAG(SUM(i.quantity * i.unit_price))OVER(ORDERBY DATE_FORMAT(o.ordered_at,'%Y-%m'))-1),1)AS growth_pct
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBYmonthORDERBYmonth;
GROUP BY month makes three rows: February 2005.00, March 464.00, April 1132.00. The window functions run over those grouped rows.
LAG(…, 1, 0) returns 0 instead of NULL for February, which has no previous month.
growth_pct uses the plain LAG (no default) so February's growth is NULL rather than a division by zero: March is 464 / 2005 - 1 = -76.9%, April 1132 / 464 - 1 = +144.0%.
The window's ORDER BY repeats the expression DATE_FORMAT(…) rather than the alias month; MySQL accepts either here, but repeating the expression is the portable form.
For aggregate window functions (SUM, AVG, MIN, MAX, COUNT…) and FIRST_VALUE/LAST_VALUE/NTH_VALUE, the window isn't necessarily the whole partition: it's a frame, a sliding range of rows around the current row. The frame is what makes SUM a running total or AVG a moving average.
The syntax is ROWS or RANGEBETWEEN start AND end, where the bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING. Picture a magnifying glass sliding down the ordered rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a glass three rows tall, positioned so the current row is at the bottom.
Two defaults explain most surprises:
No ORDER BY in OVER: the frame is the whole partition.
With ORDER BY: the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — from the start up to the current row and all its peers (rows with the same ORDER BY value).
SQL
SELECT name, salary,SUM(salary)OVER(ORDERBY salary)AS default_frame,SUM(salary)OVER(ORDERBY salary ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AS rows_frame,SUM(salary)OVER(ORDERBY salary, id ROWSUNBOUNDEDPRECEDING)AS rows_with_tiebreak
FROM employees
WHERE department_id =1ORDERBY salary, id;
Engineering ordered by salary ascending: Dev 118000, Ben 142000, Chloe 142000, Asha 185000. Ben and Chloe are peers — equal under ORDER BY salary.
default_frame uses RANGE … CURRENT ROW, which includes the current row's peers. On Ben's row the frame is Dev, Ben and Chloe: 118000 + 142000 + 142000 = 402000. Chloe's row gets the same 402000. The running total jumps over the tie in one step.
rows_frame counts physical rows: Ben's frame is Dev and Ben (260000), Chloe's adds Chloe (402000). But which of the two comes first is arbitrary when they're tied, so these per-row values could swap between runs.
rows_with_tiebreak orders by salary, id and uses ROWS, so every row has a unique position: Ben (id 2) is always 260000 and Chloe 402000. This is the running total most people actually want.
All three agree on the last row: 587000.00, Engineering's payroll.
AdvancedMoving averages with ROWS frames
Moving averages
A fixed-size ROWS frame gives a moving average — here over each order and the two before it:
SQL
WITH order_totals AS(SELECT o.id,DATE(o.ordered_at)AS ordered_on,SUM(i.quantity * i.unit_price)AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY o.id, ordered_on
)SELECT id, ordered_on, total,ROUND(AVG(total)OVER(ORDERBY ordered_on ROWSBETWEEN2PRECEDINGANDCURRENTROW),2)AS moving_avg_3,COUNT(*)OVER(ORDERBY ordered_on ROWSBETWEEN2PRECEDINGANDCURRENTROW)AS rows_in_frame
FROM order_totals
ORDERBY ordered_on;
On the first order, the frame can only contain that one row (there are no preceding rows), so the "3-order average" is just 957.00 and rows_in_frame is 1.
On the second order, the frame has two rows: (957 + 1048) / 2 = 1002.50.
From the third order on, the frame is always three rows: order 5's average is (957 + 1048 + 127) / 3 = 710.67.
On the last row (order 8): (249 + 1016 + 116) / 3 = 460.33. The glass has slid down, so orders 1, 3, 5 and 2 are no longer in it.
rows_in_frame is worth showing in reports: the first values of a moving average are based on fewer rows, which is easy to misread.
AdvancedRolling 30 days with RANGE INTERVAL
RANGE with an interval: a rolling 30 days
RANGE with a numeric or temporal offset defines the frame by value, not row count. A rolling 30-day revenue, no matter how many orders fall in that period:
SQL
WITH order_totals AS(SELECT o.id, o.ordered_at,SUM(i.quantity * i.unit_price)AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status<>'cancelled'GROUPBY o.id, o.ordered_at
)SELECT id, ordered_at, total,SUM(total)OVER(ORDERBY ordered_at
RANGE BETWEENINTERVAL30DAYPRECEDINGANDCURRENTROW)AS last_30_days
FROM order_totals
ORDERBY ordered_at;
For each order, the frame is every order whose ordered_at is within 30 days before it (and the current row).
Order 5 on 2025-03-01: the window goes back to 2025-01-30, so orders 1 (02-01) and 3 (02-10) are included: 957 + 1048 + 127 = 2132.00.
Order 2 on 2025-03-15 looks back to 2025-02-13: order 1 (02-01) and order 3 (02-10) have dropped out; orders 5 and 2 remain: 127 + 88 = 215.00.
Order 7 on 2025-04-02 covers 03-03 onwards: orders 2, 6 and 7 → 88 + 249 + 1016 = 1353.00.
A ROWS BETWEEN 2 PRECEDING frame would always include exactly three orders no matter how far apart they were; RANGE INTERVAL follows the calendar. It requires a single ORDER BY expression of a numeric or date/time type.
AdvancedThe LAST_VALUE trap
FIRST_VALUE and LAST_VALUE return a value from the first/last row of the frame. With the default frame (ending at the current row), LAST_VALUE just returns the current row:
SQL
SELECT name, salary,
FIRST_VALUE(name)OVER w AS top_earner,
LAST_VALUE(name)OVER w AS last_value_default,
LAST_VALUE(name)OVER(w ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)AS lowest_earner
FROM employees
WHERE department_id =2
WINDOW w AS(ORDERBY salary DESC);
Result
+-------+-----------+------------+--------------------+---------------+
| name | salary | top_earner | last_value_default | lowest_earner |
+-------+-----------+------------+--------------------+---------------+
| Elena | 130000.00 | Elena | Elena | Farid |
| Grace | 99000.00 | Elena | Grace | Farid |
| Farid | 95000.00 | Elena | Farid | Farid |
+-------+-----------+------------+--------------------+---------------+
3 rows in set (0.000 sec)
What’s happening
Sales ordered by salary descending: Elena, Grace, Farid. With the default frame, each row's frame runs from Elena down to the current row.
FIRST_VALUE(name) is the first row of that frame on every row: Elena, the top earner. Works as expected.
LAST_VALUE(name) with the default frame is the last row of the frame — which is the current row itself: Elena, Grace, Farid. That's the trap.
Extending the frame to UNBOUNDED FOLLOWING makes the frame the whole partition, so LAST_VALUE is Farid (95000) on every row.
Often clearer: FIRST_VALUE(name) OVER (ORDER BY salary ASC) for the lowest earner, which works with the default frame.
AdvancedGROUPS frames aren't supported
The SQL standard's third frame unit, GROUPS (count peer groups instead of rows), isn't implemented in MySQL 9.7, although PostgreSQL has it:
SQL
SELECT name, salary,SUM(salary)OVER(ORDERBY salary GROUPS BETWEEN1PRECEDINGANDCURRENTROW)AS s
FROM employees;
Result
ERROR 1235 (42000): This version of MySQL doesn't yet support 'GROUPS'
What’s happening
MySQL parses the keyword (it's been reserved since 8.0.2) but rejects it: ERROR 1235 (42000): This version of MySQL doesn't yet support 'GROUPS'.
In PostgreSQL 11+ the same frame means "the current peer group and the one before it" — on Engineering, Ben's and Chloe's rows would both include Dev's group and their own.
In MySQL, use RANGE with a value offset when the order key is numeric, or pre-aggregate the peer groups and use ROWS.
?Predict the result
SQL
SELECT name, salary,MAX(salary)OVER(ORDERBY salary DESC)AS a,MIN(salary)OVER(ORDERBY salary DESC)AS b
FROM employees
WHERE department_id =1;
Show answer
a is 185000.00 on every row; b shrinks down the list: 185000, 142000, 142000, 118000.
SQL
SELECT name, salary,MAX(salary)OVER(ORDERBY salary DESC)AS a,MIN(salary)OVER(ORDERBY salary DESC)AS b
FROM employees
WHERE department_id =1;
Result
+-------+-----------+-----------+-----------+
| name | salary | a | b |
+-------+-----------+-----------+-----------+
| Asha | 185000.00 | 185000.00 | 185000.00 |
| Ben | 142000.00 | 185000.00 | 142000.00 |
| Chloe | 142000.00 | 185000.00 | 142000.00 |
| Dev | 118000.00 | 185000.00 | 118000.00 |
+-------+-----------+-----------+-----------+
4 rows in set (0.000 sec)
What’s happening
With ORDER BY in OVER, the default frame is "from the first row up to the current row and its peers".
Ordered by salary descending, the first row is always Asha (185000), so MAX over any frame starting there is 185000.00.
MIN over "Asha up to the current row" is the current row's salary (it's the smallest seen so far). On Ben's row the frame includes his peer Chloe, but she has the same salary, so b is 142000.00 for both.
Without ORDER BY in OVER, both would be the department's overall max and min on every row.
"The two highest-paid employees in each department", "each customer's latest order", "the best-selling product per category" — top-N-per-group is probably the most asked SQL interview problem, because GROUP BY alone can't do it: grouping gives you the value of MAX(salary), not the row that has it, and certainly not the top two rows.
The modern recipe has two steps: number the rows within each group with a window function, then keep the rows whose number is ≤ N. Because window functions can't appear in WHERE, the numbering goes in a CTE (or derived table) and the filter goes outside.
SQL
WITH ranked AS(SELECT e.name, d.name AS department, e.salary,
ROW_NUMBER()OVER(PARTITIONBY e.department_id ORDERBY e.salary DESC, e.id)AS rn
FROM employees e
JOIN departments d ON d.id = e.department_id
)SELECT department, name, salary, rn
FROM ranked
WHERE rn <=2ORDERBY department, rn;
Result
+-------------+-------+-----------+----+
| department | name | salary | rn |
+-------------+-------+-----------+----+
| Engineering | Asha | 185000.00 | 1 |
| Engineering | Ben | 142000.00 | 2 |
| Sales | Elena | 130000.00 | 1 |
| Sales | Grace | 99000.00 | 2 |
| Support | Hiro | 88000.00 | 1 |
| Support | Isla | 72000.00 | 2 |
+-------------+-------+-----------+----+
6 rows in set (0.000 sec)
What’s happening
The inner join drops Jonas (no department) — decide deliberately whether he should count; a LEFT JOIN would give him his own partition.
ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC, e.id) numbers each department's employees from the highest salary down; the id tie-breaker makes Ben (id 2) 2 and Chloe (id 3) 3.
The CTE result has all nine department members with their numbers. The outer WHERE rn <= 2 — allowed now, because rn is just a column of ranked — keeps two per department.
Engineering: Asha and Ben. Sales: Elena and Grace. Support: Hiro and Isla.
Chloe earns exactly what Ben earns but is cut, purely because her id is larger. Whether that's right is a business question — which leads to the next example.
AdvancedTies: which ranking function to use
Ties: ROW_NUMBER vs RANK vs DENSE_RANK
Swap the numbering function and "top 2" changes meaning:
SQL
WITH ranked AS(SELECT e.name, e.department_id, e.salary,
ROW_NUMBER()OVER w AS rn,
RANK()OVER w AS rnk,
DENSE_RANK()OVER w AS dense_rnk
FROM employees e
WHERE e.department_id =1
WINDOW w AS(PARTITIONBY e.department_id ORDERBY e.salary DESC))SELECT name, salary, rn, rnk, dense_rnk,
rn <=2AS top2_rows, rnk <=2AS top2_rank, dense_rnk <=2AS top2_levels
FROM ranked
ORDERBY salary DESC, name;
Engineering only, to focus on the Ben/Chloe tie. In MySQL, a comparison like rn <= 2 returns 1 or 0, so the last three columns show who each rule keeps.
rn <= 2 (ROW_NUMBER) keeps exactly two rows: Asha and one of Ben/Chloe — without a tie-breaker, which one is arbitrary.
rnk <= 2 (RANK) keeps Asha, Ben and Chloe: both tied rows have rank 2. "Top 2" can return more than two rows when there are ties.
dense_rnk <= 2 (DENSE_RANK) also keeps all three here — but it means "the top two salary levels". If two more people earned 142000, RANK <= 2 and DENSE_RANK <= 2 would both keep them; with a tie at the top, RANK would skip rank 2 while DENSE_RANK would keep the next level too.
Interview tip: say out loud which one you're using and why — "exactly N rows" (ROW_NUMBER with a tie-breaker), "N places, ties included" (RANK), "N distinct values" (DENSE_RANK).
AdvancedMost expensive product per category
Top 1 per group: the most expensive product in each category
SQL
WITH ranked AS(SELECT c.name AS category, p.name AS product, p.price,
ROW_NUMBER()OVER(PARTITIONBY p.category_id ORDERBY p.price DESC, p.id)AS rn
FROM products p
JOIN categories c ON c.id = p.category_id
)SELECT category, product, price
FROM ranked
WHERE rn =1ORDERBY price DESC;
Within each, ORDER BY p.price DESC puts the priciest first, and rn = 1 keeps just that one.
Result: MacBook Air (1299.00), iPhone 17 (1099.00), Noise-cancelling Headphones (249.00) and Designing Data-Intensive Applications (49.00).
The iPhone 17 shows up even though its stock is 0 — add WHERE p.stock > 0inside the CTE if out-of-stock items shouldn't compete. Filtering outside after numbering would just remove the winner and leave that category empty.
Interviewers often ask for an alternative, or you may be on MySQL 5.7 without window functions. Here are three more ways to get each customer's latest order:
SQL
-- 1. Correlated subquery (works on any version)SELECT o.customer_id, o.id, o.ordered_at
FROM orders o
WHERE o.ordered_at =(SELECTMAX(o2.ordered_at)FROM orders o2 WHERE o2.customer_id = o.customer_id)ORDERBY o.customer_id;-- 2. Anti-join: "no later order exists for this customer" (the classic 5.7 answer)SELECT o.customer_id, o.id, o.ordered_at
FROM orders o
LEFTJOIN orders later
ON later.customer_id = o.customer_id AND later.ordered_at > o.ordered_at
WHERE later.id ISNULLORDERBY o.customer_id;-- 3. LATERAL with ORDER BY … LIMIT (MySQL 8.0.14+)SELECT c.id AS customer_id, latest.id, latest.ordered_at
FROM customers c
JOIN LATERAL (SELECT o.id, o.ordered_at FROM orders o
WHERE o.customer_id = c.id
ORDERBY o.ordered_at DESCLIMIT1)AS latest ONTRUEORDERBY c.id;
All three return the same five rows: customer 1's order 2, customer 2's order 8, customer 3's order 5, customer 4's order 6, customer 5's order 7.
Correlated subquery: keep an order if its date equals the customer's maximum date. Simple, but ties at the maximum return several rows, and without an index on (customer_id, ordered_at) it re-scans per row.
Anti-join: join each order to every later order of the same customer; the latest order has no later one, so its later.id is NULL. It's elegant but builds many intermediate rows for customers with lots of orders (every order is joined to all its successors).
LATERAL: for each customer, run "their orders, newest first, take 1". With an index on (customer_id, ordered_at) each lookup reads one index entry — usually the fastest for top-1 and small N, and it easily becomes top-3 with LIMIT 3.
The ROW_NUMBER CTE remains the most general (any N, any tie rule) and the one to write first in an interview.
AdvancedQUALIFY and PostgreSQL's DISTINCT ON
QUALIFY, and PostgreSQL's DISTINCT ON
Some databases (Snowflake, BigQuery, DuckDB, Teradata) let you filter on a window function directly with QUALIFY. MySQL 9.7 doesn't document it — but its parser knows the keyword and accepts it when the new hypergraph optimizer is switched on, which we found by trying:
ERROR 6037 (HY000): 'QUALIFY clause' can be used only if the hypergraph optimizer is enabled.
Query OK, 0 rows affected (0.000 sec)
+-------+---------------+-----------+
| name | department_id | salary |
+-------+---------------+-----------+
| Jonas | NULL | 91000.00 |
| Asha | 1 | 185000.00 |
| Elena | 2 | 130000.00 |
| Hiro | 3 | 88000.00 |
+-------+---------------+-----------+
4 rows in set (0.000 sec)
What’s happening
With the default optimizer, MySQL 9.7.2 refuses: ERROR 6037 (HY000): 'QUALIFY clause' can be used only if the hypergraph optimizer is enabled.
After SET optimizer_switch = 'hypergraph_optimizer=on' (session only), the same query returns the top earner of each department, NULL department included.
Because it isn't in the 9.7 reference manual, treat it as an implementation detail: fine to know, not something to put in production code. The CTE + WHERE rn = 1 form works everywhere.
PostgreSQL has a shortcut for the top-1 case, DISTINCT ON, which keeps the first row of each group in the ORDER BY order:
name isn't grouped or aggregated, so MySQL 9.7 rejects it with ERROR 1055; with ONLY_FULL_GROUP_BY off, it would return an arbitrary name next to the maximum. MAX gives the top value, never the row it came from.
SQL
WITH r AS(SELECT department_id, name, salary,
RANK()OVER(PARTITIONBY department_id ORDERBY salary DESC)AS rnk
FROM employees
)SELECT department_id, name, salary FROM r WHERE rnk =1ORDERBY department_id;
Result
+---------------+-------+-----------+
| department_id | name | salary |
+---------------+-------+-----------+
| NULL | Jonas | 91000.00 |
| 1 | Asha | 185000.00 |
| 2 | Elena | 130000.00 |
| 3 | Hiro | 88000.00 |
+---------------+-------+-----------+
4 rows in set (0.000 sec)
What’s happening
RANK() numbers each department's employees from the top salary; ties at the top would all get 1.
WHERE rnk = 1 keeps the top row(s) per department, with the right name attached: Jonas (NULL department), Asha, Elena and Hiro.
This is the general fix for "the row behind an aggregate" — see also ONLY_FULL_GROUP_BY.
Set operations combine the results of two queries vertically — stacking rows — where joins combine tables horizontally. Both inputs must have the same number of columns with compatible types, and the result takes its column names from the first query.
UNION — rows in either result (duplicates removed); UNION ALL keeps duplicates.
INTERSECT — rows in both results.
EXCEPT — rows in the first result but not the second (Oracle calls it MINUS).
UNION has been in MySQL forever; INTERSECT and EXCEPT arrived in MySQL 8.0.31, so you'll still see older code emulating them with EXISTS and NOT EXISTS. Think of them as Venn diagrams over whole rows: the two queries are two circles, and each operator shades a different region.
SQL
-- Customers who ordered in February 2025SELECT customer_id FROM orders WHERE ordered_at >='2025-02-01'AND ordered_at <'2025-03-01'INTERSECT-- ... and also in March 2025SELECT customer_id FROM orders WHERE ordered_at >='2025-03-01'AND ordered_at <'2025-04-01';SELECT customer_id FROM orders WHERE ordered_at >='2025-02-01'AND ordered_at <'2025-03-01'EXCEPTSELECT customer_id FROM orders WHERE ordered_at >='2025-03-01'AND ordered_at <'2025-04-01';
Result
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
2 rows in set (0.000 sec)
+-------------+
| customer_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.000 sec)
INTERSECT keeps values present in both: customers 1 (Rohit) and 3 (Liam) ordered in both months.
EXCEPT keeps February's customers that aren't in March: 2 (Maya) — a "lapsed" customer.
Like UNION, both remove duplicates by default (DISTINCT is the default). INTERSECT ALL and EXCEPT ALL (also 8.0.31+) keep duplicates, counting them: if customer 1 had two February orders and one in March, INTERSECT ALL would return 1 once.
The sort order of a set operation's result isn't guaranteed; add ORDER BY at the end when it matters.
AdvancedUNION vs UNION ALL
UNION has to remove duplicates, which means sorting or hashing the combined result. UNION ALL just appends. A mailing list of everyone in the database:
SQL
SELECT name,'customer'AS kind FROM customers WHERE city ='Toronto'UNIONALLSELECT name,'employee'FROM employees WHERE department_id =3ORDERBY kind, name;SELECT city FROM customers
UNIONSELECT city FROM customers;SELECT city FROM customers
UNIONALLSELECT city FROM customers;
Result
+-------+----------+
| name | kind |
+-------+----------+
| Noah | customer |
| Rohit | customer |
| Hiro | employee |
| Isla | employee |
+-------+----------+
4 rows in set (0.000 sec)
+---------+
| city |
+---------+
| Toronto |
| Mumbai |
| London |
| NULL |
| Dubai |
+---------+
5 rows in set (0.000 sec)
+---------+
| city |
+---------+
| Toronto |
| Mumbai |
| London |
| NULL |
| Toronto |
| Dubai |
| Toronto |
| Mumbai |
| London |
| NULL |
| Toronto |
| Dubai |
+---------+
12 rows in set (0.000 sec)
What’s happening
The first query stacks two Toronto customers and two Support employees. The column names (name, kind) come from the first SELECT; the second's literal 'employee' lands in kind by position, not by name.
ORDER BY kind, name after the last query sorts the whole result, and must use the first query's column names.
city UNION city collapses the twelve values to the five distinct ones — including one NULL. For set operations (as for DISTINCT and GROUP BY), two NULLs count as duplicates.
UNION ALL returns all twelve rows, duplicates included.
Use UNION ALL whenever duplicates are impossible or wanted: it skips the de-duplication work. Plain UNION "just in case" is a common, silent performance cost on big results.
AdvancedPrecedence and pre-8.0.31 emulation
Precedence, and the pre-8.0.31 way
INTERSECT binds tighter than UNION and EXCEPT, just as AND binds tighter than OR. Use parentheses to be explicit. Before 8.0.31 — and in a lot of code still running — the same results came from semi-joins and anti-joins:
SQL
-- INTERSECT without INTERSECTSELECTDISTINCT f.customer_id
FROM orders f
WHERE f.ordered_at >='2025-02-01'AND f.ordered_at <'2025-03-01'ANDEXISTS(SELECT1FROM orders m
WHERE m.customer_id = f.customer_id
AND m.ordered_at >='2025-03-01'AND m.ordered_at <'2025-04-01');-- EXCEPT without EXCEPTSELECTDISTINCT f.customer_id
FROM orders f
WHERE f.ordered_at >='2025-02-01'AND f.ordered_at <'2025-03-01'ANDNOTEXISTS(SELECT1FROM orders m
WHERE m.customer_id = f.customer_id
AND m.ordered_at >='2025-03-01'AND m.ordered_at <'2025-04-01');
Result
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
2 rows in set (0.000 sec)
+-------------+
| customer_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.000 sec)
What’s happening
The EXISTS version keeps February customers for whom a March order exists: 1 and 3, the same as INTERSECT.
The NOT EXISTS version keeps February customers with no March order: 2, the same as EXCEPT.
DISTINCT is needed to mimic the set operators' duplicate removal.
One subtle difference: INTERSECT/EXCEPT treat NULLs as equal to each other, while m.customer_id = f.customer_id never matches NULL. With nullable columns, use <=> in the emulation.
AdvancedThe TABLE statement (8.0.19+)
The TABLE statement
MySQL 8.0.19 added TABLE t as shorthand for SELECT * FROM t, which reads nicely with set operations on whole tables:
SQL
CREATETEMPORARYTABLE feb_customers ASSELECTDISTINCT customer_id FROM orders WHERE ordered_at <'2025-03-01';TABLE feb_customers
UNIONVALUESROW(6);