Subqueries in WHERE
35 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Show the products priced above the average price. You do not know the average yet, so you would need one query to find it and another to use it. Can SQL do both in one statement?
A query inside a query
A subquery is a complete SELECT written in brackets inside another SQL statement. The inner query works out a value or a list; the query around it, the outer query, uses that answer.
Which products cost more than the average price? The average must be worked out first, then each product is compared with it. A subquery does both in one statement.
SELECT name, priceFROM productsWHERE price > AVG(price);ERROR 1111 (HY000): Invalid use of group function
Example 1 · Products that cost more than the average
-- Step 1 on its own: the average price of all 15 productsSELECT AVG(price) AS avg_priceFROM products;
-- Both steps in one query: the subquery works out the average,-- the outer query compares every product with itSELECT name, priceFROM productsWHERE price > (SELECT AVG(price) FROM products)ORDER BY price DESC;+--------------+ | avg_price | +--------------+ | 15064.666667 | +--------------+ +-----------------------+----------+ | name | price | +-----------------------+----------+ | iPhone 15 | 69900.00 | | HP Victus Gaming | 64990.00 | | Lenovo IdeaPad Slim 3 | 42990.00 | | Samsung Galaxy M35 | 19999.00 | | Redmi Note 13 | 17999.00 | +-----------------------+----------+
What this code does
- The first query shows what the inner query returns on its own: one value, about 15064.67. Running the inner query alone like this is the best way to check a subquery.
- In the second query MySQL works out
(SELECT AVG(price) FROM products)first, then keeps every product whose price is greater. Only five products, the phones and laptops, cost more than the average. - If a laptop's price changes tomorrow, the same query uses the new average. Nothing is typed by hand.
The first idea most people try fails. WHERE checks one row at a time, before any grouping happens, so it cannot use an aggregate such as AVG that needs the whole table. MySQL stops with error 1111, Invalid use of group function. You could run SELECT AVG(price) FROM products;, read 15064.67 and type that number into a second query. But the number goes stale the moment a price changes.
Comparing with a single value
A scalar subquery returns exactly one row with one column, a single value. It can be used wherever a single value fits, such as after =, < or >.
The highest salary in a department, or the date one customer joined: one value decides which rows to keep.
Example 2 · Highest salary, and ties with =
-- Who earns the most in the company?SELECT name, salaryFROM employeesWHERE salary = (SELECT MAX(salary) FROM employees);
-- Who earns the least in Sales (dept_id 2)? Two people tie.SELECT name, salaryFROM employeesWHERE dept_id = 2 AND salary = (SELECT MIN(salary) FROM employees WHERE dept_id = 2)ORDER BY name;
-- ORDER BY with LIMIT 1 hides the tieSELECT name, salaryFROM employeesWHERE dept_id = 2ORDER BY salary, nameLIMIT 1;+------------+-----------+ | name | salary | +------------+-----------+ | Vikram Rao | 250000.00 | +------------+-----------+ +-----------+----------+ | name | salary | +-----------+----------+ | Amit Shah | 85000.00 | | Farah Ali | 85000.00 | +-----------+----------+ +-----------+----------+ | name | salary | +-----------+----------+ | Amit Shah | 85000.00 | +-----------+----------+
What this code does
MAX(salary)returns one value, 250000.00, so=is safe. The outer query finds the row with that salary.- The inner query of the second query has its own
WHERE dept_id = 2, so it finds the lowest salary in Sales only. Amit Shah and Farah Ali both earn 85000.00, and=returns both. - The
LIMIT 1version returns only Amit Shah. If the question is "who earns the least", the subquery gives the honest answer.
Example 3 · A value from another row, and an empty subquery
-- Customers who joined after Kabir SinghSELECT name, city, joined_onFROM customersWHERE joined_on > (SELECT joined_on FROM customers WHERE name = 'Kabir Singh')ORDER BY joined_on;
-- A typo in the name: the subquery finds no row, so its value is NULLSELECT (SELECT joined_on FROM customers WHERE name = 'Kabir Sing') AS kabir_joined;+--------------+-----------+------------+ | name | city | joined_on | +--------------+-----------+------------+ | Priya Sharma | Pune | 2025-02-02 | | Arjun Mehta | Mumbai | 2025-02-14 | | Divya Nair | Kochi | 2025-03-01 | | Rahul Verma | Delhi | 2025-03-11 | | Sneha Patil | Pune | 2025-04-05 | | Imran Khan | Hyderabad | 2025-04-18 | +--------------+-----------+------------+ +--------------+ | kabir_joined | +--------------+ | NULL | +--------------+
What this code does
- The inner query looks up one row and returns Kabir's join date, 2025-01-20. The outer query then lists the six customers who joined later.
- With the misspelled name
'Kabir Sing', the inner query returns no row, and a scalar subquery with no row isNULL. The outer query from the first part would then comparejoined_on > NULL, which is never true, and return nothing, with no error to warn you.
Aggregates such as MAX, MIN, AVG and COUNT without GROUP BY always return one row, so they make safe scalar subqueries. A lookup by a unique column, such as one customer's join date by email or name, also returns one value. Two details matter. If several rows tie with the value, = returns all of them, while ORDER BY ... LIMIT 1 would hide the tie. And if the subquery finds no row at all, its value is NULL. Any comparison with NULL is unknown, so the outer query quietly returns no rows instead of an error.
Predict the output
Predict the scalar subquery
Use the 15 products of the shop. iPhone 15 costs the most (69900.00).
Round 1 of 2
IN with a subquery
IN (subquery) is true when a value appears anywhere in the list the subquery returns. The subquery must return one column, but it may return any number of rows.
Keep the rows whose value is in a list that another query produces. The list can have one value, many values or none.
Example 4 · IN: passengers on trains to Chennai
DROP TABLE IF EXISTS train_bookings, trains;
CREATE TABLE trains ( train_no INT PRIMARY KEY, train_name VARCHAR(40) NOT NULL, to_station VARCHAR(30) NOT NULL);
CREATE TABLE train_bookings ( pnr BIGINT PRIMARY KEY, passenger VARCHAR(40) NOT NULL, train_no INT NOT NULL);
INSERT INTO trains VALUES (12007, 'Shatabdi Express', 'Chennai'), (12658, 'Chennai Mail', 'Chennai'), (12627, 'Karnataka Express', 'Delhi'), (16526, 'Island Express', 'Kanyakumari');
INSERT INTO train_bookings VALUES (4512300001, 'Lakshmi', 12007), (4512300002, 'Farhan', 12627), (4512300003, 'Gopal', 12658), (4512300004, 'Nandini', 16526), (4512300005, 'Joseph', 12658);
-- Which trains go to Chennai? (the inner query alone)SELECT train_no FROM trains WHERE to_station = 'Chennai' ORDER BY train_no;
-- Passengers booked on any of those trainsSELECT passenger, train_noFROM train_bookingsWHERE train_no IN (SELECT train_no FROM trains WHERE to_station = 'Chennai')ORDER BY passenger;+----------+ | train_no | +----------+ | 12007 | | 12658 | +----------+ +-----------+----------+ | passenger | train_no | +-----------+----------+ | Gopal | 12658 | | Joseph | 12658 | | Lakshmi | 12007 | +-----------+----------+
What this code does
- The inner query returns a list of two train numbers, 12007 and 12658.
INkeeps a booking when itstrain_nois anywhere in that list. - Three passengers match: Lakshmi on the Shatabdi and Gopal and Joseph on the Chennai Mail. If another Chennai train is added to
trains, its bookings show up with no change to the query.
Example 5 · Nested IN versus a join: who bought boAt Airdopes?
-- Two levels: order_items -> orders -> customersSELECT nameFROM customersWHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 6))ORDER BY name;
-- The same question with joins repeats a customer once per orderSELECT c.nameFROM customers cJOIN orders o ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id = o.order_idWHERE oi.product_id = 6ORDER BY c.name;+-------------+ | name | +-------------+ | Asha Rao | | Rahul Verma | | Ravi Kumar | +-------------+ +-------------+ | name | +-------------+ | Asha Rao | | Rahul Verma | | Ravi Kumar | | Ravi Kumar | +-------------+
What this code does
- Read nested subqueries from the inside out. The innermost query finds the orders that contain product 6 (orders 1, 7, 11 and 15). The middle one turns those orders into customer ids. The outer query prints the names.
- Ravi Kumar bought the Airdopes in two different orders.
INlists him once, because it only checks membership. The join pairs him with both orders, so he appears twice. - To remove the repeat from the join you would add
DISTINCT. WithINthere is nothing to remove.
IN (subquery) is how you filter one table by facts stored in another: customers who placed an order in April, passengers booked on trains to Chennai, students enrolled in a course. A subquery can even contain another subquery, so you can follow the keys through two or three tables. IN only asks "is it in the list?", so each outer row appears at most once. A join with the same tables can repeat a row once per match. That makes IN a good choice when you only need columns from the outer table.
Order of execution
Put the nested query in order
Order the lines of this query: customers who bought product 6.
NOT IN (subquery) keeps rows that are not in the list, but it has a dangerous trap when the list contains NULL. The EXISTS lesson in this module explains it and the safe alternative.The returns more than 1 row error
With =, < or >, MySQL expects the subquery to return one value. If it returns two or more rows, MySQL stops with error 1242, Subquery returns more than 1 row.
SELECT name, priceFROM productsWHERE price = (SELECT price FROM products WHERE category_id = 5);ERROR 1242 (21000): Subquery returns more than 1 row
Example 6 · Two fixes for error 1242
-- Fix 1: any of the Books prices may match, so use INSELECT name, priceFROM productsWHERE price IN (SELECT price FROM products WHERE category_id = 5)ORDER BY price;
-- Fix 2: you meant one value, so ask for one.-- Products cheaper than the costliest book:SELECT name, priceFROM productsWHERE price < (SELECT MAX(price) FROM products WHERE category_id = 5)ORDER BY price;+-----------------+--------+ | name | price | +-----------------+--------+ | Atomic Habits | 399.00 | | Clean Code | 499.00 | | Hands-On Python | 650.00 | +-----------------+--------+ +-------------------+--------+ | name | price | +-------------------+--------+ | Rubik's Cube | 299.00 | | Atomic Habits | 399.00 | | Clean Code | 499.00 | | Gift Card Rs. 500 | 500.00 | +-------------------+--------+
What this code does
- Fix 1 turns "equal to the three prices" into "equal to any price in the list". Only the three books have those exact prices.
- Fix 2 shows the other meaning: compare with one chosen value.
MAX(price)of the Books is 650.00, so the outer query lists everything cheaper than 650. - Both queries run without error because each comparison now has the right shape: a list for
IN, one value for<.
Here the three Books prices (499, 650 and 399) are three values, and = cannot compare one price with three.
Pick the fix by what you meant. If any of the values should match, use IN. If you wanted one value from the group, ask for it clearly with MAX, MIN or AVG. Avoid adding LIMIT 1 just to silence the error: it picks an unpredictable row and hides the real question. A related error is 1241, Operand should contain 1 column(s). It appears when the subquery selects two columns, such as (SELECT price, name FROM ...), where one value is expected. Select only the column you compare.
Fix the error
Fix the error 1242
Electronics has three sub-categories. Pick the line that makes the query run.
List the products of every sub-category of Electronics.
Error
ERROR 1242 (21000): Subquery returns more than 1 row
= only when the subquery can return at most one row by design: an aggregate or a lookup on a unique column.Next step
Check your understanding
12 quick questions