SQL & Databases
Topic 8 · Querying data

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.

Show:
In this topic
Beginner

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:

SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) ORDER BY price DESC;

The inner query runs and returns one number, and the outer query uses it like a constant.

Beginner

Three shapes of subquery

ReturnsUsed withExample
one value (a scalar)= < >, or as a columnWHERE price > (SELECT AVG(price) FROM products)
a list (one column)IN, NOT INWHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'pending')
a tableFROMFROM (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.

Beginner

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…?":

-- customers who have at least one cancelled order SELECT c.name FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id AND o.status = 'cancelled');

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.

Beginner

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 ALL keeps 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).
-- one list of every city and country we know about SELECT city AS place FROM customers WHERE city IS NOT NULL UNION SELECT country FROM customers ORDER BY place;

The column names come from the first query, and ORDER BY goes once, at the very end.

Try it

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.

Beginner

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)

Exercise 1. Above average

Show the name and price of products that cost more than the average product price.

Exercise 2. Customers with a cancelled order

Show the name of every customer who has at least one cancelled order, using EXISTS or IN.

Exercise 3. Every place

Make one column called place listing every distinct city (not NULL) and every country of the customers, with no duplicates.

Beginner

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).
Check yourself

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…

Ready