Subqueries & Set Operations
Use the answer of one query inside another: subqueries that return one value, a list or a whole table, EXISTS and NOT EXISTS, correlated subqueries, and stacking results with UNION, INTERSECT and EXCEPT.
A query inside a query
A subquery is a SELECT in brackets, used inside another query. It lets you answer questions in steps. "Which products cost more than the average?" needs the average first:
The inner query runs and returns one number, and the outer query uses it like a constant.
Three shapes of subquery
| Returns | Used with | Example |
|---|---|---|
| one value (a scalar) | = < >, or as a column | WHERE price > (SELECT AVG(price) FROM products) |
| a list (one column) | IN, NOT IN | WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'pending') |
| a table | FROM | FROM (SELECT … GROUP BY …) AS t |
A subquery in FROM is called a derived table and needs an alias. It is how you aggregate twice: first totals per order, then the average of those totals.
EXISTS and NOT EXISTS
EXISTS (subquery) is true when the subquery returns at least one row. It is the clearest way to ask "is there any…?":
This subquery is correlated: it refers to c from the outer query, so it is checked once per customer. NOT EXISTS finds rows with no match, like the anti-join.
Careful with NOT IN: if the subquery's list contains a NULL, NOT IN returns no rows at all (because "x is not equal to unknown" is unknown). NOT EXISTS does not have this problem, so prefer it.
UNION, INTERSECT and EXCEPT
Set operations stack the results of two queries with the same number of columns (and compatible types):
UNION: rows from either query, duplicates removed.UNION ALLkeeps duplicates and is faster: use it unless you need duplicates removed.INTERSECT: rows in both.EXCEPT: rows in the first but not the second (Oracle calls it MINUS).
The column names come from the first query, and ORDER BY goes once, at the very end.
Try it: subqueries and sets
Run each preset. Try changing UNION to UNION ALL and see the duplicates appear.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show the name and price of products that cost more than the average product price.
Show the name of every customer who has at least one cancelled order, using EXISTS or IN.
Make one column called place listing every distinct city (not NULL) and every country of the customers, with no duplicates.
Summary
Key words
- subquery
- a SELECT inside another statement, in brackets
- scalar subquery
- returns a single value
- derived table
- a subquery in FROM, with an alias
- correlated subquery
- refers to the outer query; runs per outer row
- EXISTS
- true if the subquery returns any row
- UNION / UNION ALL
- stack results, without / with duplicates
- INTERSECT / EXCEPT
- rows in both / in the first only
Common mistakes
- A subquery used with = that returns several rows: use IN.
- NOT IN with a list that contains NULL returns nothing: use NOT EXISTS.
- Forgetting the alias on a derived table.
- UNION of queries with different numbers of columns.
- Using UNION when UNION ALL is what you want (and paying for the duplicate check).
Quiz
1. Which subquery shape goes with IN?
2. What is the difference between UNION and UNION ALL?
3. Why prefer NOT EXISTS to NOT IN?
4. A correlated subquery…