sqlstreak

SQL subqueries

A subquery is a query in parentheses used inside another query. It lets you ask “which items cost more than average?” without hard-coding the average.

A single value

A subquery that returns exactly one value (one row, one column) can be used anywhere a value can, for example with >.

SELECT name, price
FROM menu_items
WHERE price > (SELECT AVG(price) FROM menu_items);

A list with IN

A subquery that returns one column can feed IN.

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');

EXISTS and NOT EXISTS

EXISTS asks whether the subquery returns any row at all. NOT EXISTS is the safe way to find things with no match, because unlike NOT IN it isn't tripped up by NULLs.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Correlated subqueries

A correlated subquery refers to the outer row, so it runs once per row. Here, each customer's number of orders.

SELECT c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY orders DESC;

Subqueries in FROM

A subquery in FROM (a derived table) acts like a temporary table. It needs an alias. For anything long, a CTE (WITH) reads better.

SELECT AVG(total) AS avg_order
FROM (
  SELECT oi.order_id, SUM(oi.quantity * m.price) AS total
  FROM order_items oi
  JOIN menu_items m ON m.id = oi.item_id
  GROUP BY oi.order_id
) totals;

Common mistakes

  • Using = with a subquery that returns several rows. Use IN, or aggregate to a single value.

  • NOT IN (subquery) when the subquery can return NULL: the result is empty. Use NOT EXISTS.

  • Correlated subqueries over large tables. A JOIN with GROUP BY is often much faster.

Questions

Subquery or JOIN?+

Use whichever says what you mean. Modern databases often plan them the same way. EXISTS is great for “has at least one”, JOIN when you need columns from both tables.

Subquery or CTE?+

Same power. CTEs (WITH) name each step and read top to bottom, which helps once queries get long.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now