Subqueries in FROM and SELECT
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A subquery is not limited to WHERE. In FROM its result becomes a temporary table you can group and filter again, and in the SELECT list it adds one computed value to every row.
Derived tables in FROM
A derived table is a subquery written in the FROM clause. Its result is a temporary table that exists only while the outer query runs, and it must have an alias.
What is the average of the order totals? An order total is itself a SUM, so first build a table of totals, then average that table.
SELECT AVG(order_total)FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total FROM order_items GROUP BY order_id);ERROR 1248 (42000): Every derived table must have its own alias
Example 1 · Average order value with a derived table
-- Inner query: one row per order with its total-- Outer query: summarise those totalsSELECT COUNT(*) AS orders, ROUND(AVG(order_total), 2) AS avg_order_value, MAX(order_total) AS biggest_orderFROM ( SELECT order_id, SUM(quantity * unit_price) AS order_total FROM order_items GROUP BY order_id) AS order_totals;+--------+-----------------+---------------+ | orders | avg_order_value | biggest_order | +--------+-----------------+---------------+ | 15 | 20622.87 | 69900.00 | +--------+-----------------+---------------+
What this code does
- The derived table
order_totalshas 15 rows, one per order, with a column calledorder_total. The outer query treats it like a real table. AVG(order_total)averages the 15 order totals, all orders including cancelled ones. This is the aggregate-of-an-aggregate thatAVG(SUM(...))cannot do in one step.AS order_totalsis required. Remove it and MySQL stops with error 1248, as the section shows.
Example 2 · Customers per spending band
-- Step 1 (derived table): spend and band for each customer-- Step 2 (outer query): count the customers in each bandSELECT band, COUNT(*) AS customers, MIN(spent) AS lowest, MAX(spent) AS highestFROM ( SELECT c.customer_id, SUM(oi.quantity * oi.unit_price) AS spent, CASE WHEN SUM(oi.quantity * oi.unit_price) >= 50000 THEN 'Gold' WHEN SUM(oi.quantity * oi.unit_price) >= 10000 THEN 'Silver' ELSE 'Bronze' END AS band FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status <> 'CANCELLED' GROUP BY c.customer_id) AS spendGROUP BY bandORDER BY MAX(spent) DESC;+--------+-----------+----------+----------+ | band | customers | lowest | highest | +--------+-----------+----------+----------+ | Gold | 2 | 64990.00 | 68834.00 | | Silver | 2 | 19298.00 | 27196.00 | | Bronze | 4 | 898.00 | 8741.00 | +--------+-----------+----------+----------+
What this code does
- The inner query groups by customer to get each person's spend on orders that were not cancelled, then labels it with
CASE. Its result has one row per customer. - The outer query groups those rows again, this time by
band. Grouping twice is exactly what a derived table is for. - Only 8 customers are counted: Meena's only order was cancelled and Imran never ordered, so the inner joins leave them out.
AVG(SUM(...)) in one query fails with error 1111, because MySQL cannot nest one aggregate inside another, so one summary built on another needs a derived table. The columns of a derived table are named by the aliases inside it, such as order_total, and the outer query uses them like ordinary columns: in SELECT, WHERE, GROUP BY and joins.
Predict the output
Predict the derived table
All 15 orders have at least one item (25 items in total).
DELETE ... WHERE price > (SELECT avg_price FROM (SELECT AVG(price) AS avg_price FROM products) AS t) works, because MySQL works out the derived table first.Scalar subqueries in SELECT
A scalar subquery in SELECT is a subquery in the column list that returns one value. MySQL puts that value into a column of every result row, next to the row's own data.
Example 3 · Each phone next to the average price
SELECT name, price, ROUND((SELECT AVG(price) FROM products), 2) AS avg_price, ROUND(price - (SELECT AVG(price) FROM products), 2) AS above_avgFROM productsWHERE category_id = 2ORDER BY price;+--------------------+----------+-----------+-----------+ | name | price | avg_price | above_avg | +--------------------+----------+-----------+-----------+ | Redmi Note 13 | 17999.00 | 15064.67 | 2934.33 | | Samsung Galaxy M35 | 19999.00 | 15064.67 | 4934.33 | | iPhone 15 | 69900.00 | 15064.67 | 54835.33 | +--------------------+----------+-----------+-----------+
What this code does
(SELECT AVG(price) FROM products)returns one value, so MySQL can place it in a column. It is the same 15064.67 on every row.- The second subquery is used inside arithmetic:
price - (...)gives how far each phone is above the shop average. - The outer
WHERE category_id = 2filters only the outer rows. The subquery still averages all 15 products, because it has noWHEREof its own.
A scalar subquery in SELECT lets you compare each row with a figure from the whole table: each phone's price next to the average price, or each order's total as a share of all revenue. Without it you would run two queries and do the arithmetic yourself.
The rule from WHERE still holds: the subquery must return at most one row and one column. More rows give error 1242, and no row gives NULL in that column.
Correlated subqueries
A correlated subquery uses a column from the outer query's current row, so its answer changes from row to row. It is run again for each outer row.
Which employees earn more than the average of their own department? The average is different for each employee, so the inner query needs the outer row.
Example 4 · Correlated: above their own department's average
-- Department averages, for referenceSELECT dept_id, ROUND(AVG(salary), 2) AS dept_avgFROM employeesGROUP BY dept_idORDER BY dept_id;
-- e = the outer employee, x = the employees in the same departmentSELECT e.name, e.dept_id, e.salaryFROM employees eWHERE e.salary > ( SELECT AVG(x.salary) FROM employees x WHERE x.dept_id = e.dept_id)ORDER BY e.dept_id, e.salary DESC;+---------+-----------+ | dept_id | dept_avg | +---------+-----------+ | NULL | 70000.00 | | 1 | 153000.00 | | 2 | 106666.67 | | 3 | 75000.00 | | 4 | 107500.00 | +---------+-----------+ +---------------+---------+-----------+ | name | dept_id | salary | +---------------+---------+-----------+ | Vikram Rao | 1 | 250000.00 | | Anita Desai | 1 | 180000.00 | | Neha Joshi | 2 | 150000.00 | | Lakshmi Menon | 3 | 90000.00 | | Gita Reddy | 4 | 140000.00 | +---------------+---------+-----------+
What this code does
- For each outer employee
e, the inner query averages only the employeesxwith the samedept_id. For Neha Joshi (Sales) it compares 150000.00 with 106666.67; for Gita Reddy (Finance) with 107500.00. - Tanvi Kulkarni has no department (
NULL). Inside her subqueryx.dept_id = NULLis never true, so the average isNULLand she is left out, even though the first query shows aNULLgroup. - The aliases
eandxare what make this work: without them MySQL could not tell the outer employee from the inner ones.
Example 5 · Correlated in SELECT: orders per customer
SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders, (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_orderFROM customers cORDER BY c.customer_id;+--------------+--------+------------+ | name | orders | last_order | +--------------+--------+------------+ | Asha Rao | 3 | 2025-04-12 | | Ravi Kumar | 3 | 2025-06-01 | | Meena Iyer | 1 | 2025-02-10 | | Kabir Singh | 2 | 2025-05-02 | | Priya Sharma | 2 | 2025-05-20 | | Arjun Mehta | 1 | 2025-03-28 | | Divya Nair | 1 | 2025-04-04 | | Rahul Verma | 1 | 2025-04-25 | | Sneha Patil | 1 | 2025-05-09 | | Imran Khan | 0 | NULL | +--------------+--------+------------+
What this code does
- Each subquery runs for the current customer
cand returns one value, so the result has exactly one row per customer. - Imran Khan has no orders:
COUNT(*)over zero rows is 0, andMAXover zero rows isNULL. He still appears, unlike with an inner join. - The same
WHERE o.customer_id = c.customer_idlookup is written twice. That repetition is a sign that a join may read better, as the next section shows.
The subqueries so far were non-correlated: they ran once and gave the same answer for every row. The classic question is "who earns more than the average of their own department?". Writing x.dept_id = e.dept_id inside the subquery ties it to the current employee e. Table aliases are essential here, because both queries read the same table. A correlated subquery in SELECT can count related rows: the number of orders for each customer. A customer with no orders gets 0 from COUNT(*), so nobody disappears from the list.
Find the bug
Find the wrong line
Each correlated subquery has one wrong line. Click it.
Show each customer's number of orders.
Round 1 of 2WHERE customer_id IN (SELECT customer_id FROM order_items) returns every customer, because order_items has no customer_id and the subquery quietly becomes correlated. Prefix columns with table aliases to avoid it.When a join is clearer
A non-correlated subquery does not use any column of the outer query, so it can run once on its own. (SELECT AVG(price) FROM products) is one.
Many subqueries can be written as a join.
Example 6 · The same answers with joins
-- Orders per customer: one LEFT JOIN instead of two subqueriesSELECT c.name, COUNT(o.order_id) AS orders, MAX(o.order_date) AS last_orderFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.nameORDER BY c.customer_id;
-- Above the department average: join a derived table of averagesSELECT e.name, d.dept_avg, e.salaryFROM employees eJOIN ( SELECT dept_id, ROUND(AVG(salary), 2) AS dept_avg FROM employees GROUP BY dept_id) AS d ON d.dept_id = e.dept_idWHERE e.salary > d.dept_avgORDER BY e.dept_id, e.salary DESC;+--------------+--------+------------+ | name | orders | last_order | +--------------+--------+------------+ | Asha Rao | 3 | 2025-04-12 | | Ravi Kumar | 3 | 2025-06-01 | | Meena Iyer | 1 | 2025-02-10 | | Kabir Singh | 2 | 2025-05-02 | | Priya Sharma | 2 | 2025-05-20 | | Arjun Mehta | 1 | 2025-03-28 | | Divya Nair | 1 | 2025-04-04 | | Rahul Verma | 1 | 2025-04-25 | | Sneha Patil | 1 | 2025-05-09 | | Imran Khan | 0 | NULL | +--------------+--------+------------+ +---------------+-----------+-----------+ | name | dept_avg | salary | +---------------+-----------+-----------+ | Vikram Rao | 153000.00 | 250000.00 | | Anita Desai | 153000.00 | 180000.00 | | Neha Joshi | 106666.67 | 150000.00 | | Lakshmi Menon | 75000.00 | 90000.00 | | Gita Reddy | 107500.00 | 140000.00 | +---------------+-----------+-----------+
What this code does
- The first query matches Example 5 row for row.
LEFT JOINkeeps Imran, andCOUNT(o.order_id)counts only real orders, so he gets 0.COUNT(*)here would wrongly give him 1. - The second query matches Example 4, and it can also show
dept_avgbecause the averages are now a table with a column you can select. - Same results, different shape. Choose the version you and your team can read fastest.
Use a join when you need several columns or totals from the other table: one LEFT JOIN with GROUP BY gives the order count, last order date and total spend together, while subqueries would repeat the same lookup three times. A derived table joined back to the main table is a strong pattern. To compare each employee with their department average, build the averages once in a derived table, join on dept_id, and you can also show the average next to the salary. Use a subquery when it says the question more directly: one value to compare with, a membership test with IN, or one small figure per row. Both styles return the same rows. MySQL 8 often rewrites one into the other internally, so pick the one that is easier to read, and measure speed only when a query is actually slow.
- Need columns from both tables → join.
- Need several totals per row →
LEFT JOIN+GROUP BY. - Need one value or a yes/no check → subquery.
- Need a summary of a summary → derived table.
Next step
Check your understanding
12 quick questions