Extra topic · Query-ception

SQL Subqueries Explained: IN (SELECT …) and NOT IN

A subquery is a query inside a query. Use the result of one SELECT to filter another with IN and NOT IN, and learn why one NULL in the list makes NOT IN return nothing.

Covers: Subqueries · IN (SELECT …) · NOT IN

Practise Subqueries

The lesson

Every example runs against the same small pizza shop database: pizzas, customers, orders and staff.

# A query inside a query You need one when a question depends on another answer first: "pizzas that cost more than the average" needs the average before it can compare. A subquery is a SELECT in brackets, inside another query. The inner one runs first, and the outer one uses its answer. Start with the inner query on its own: the average price. - `AVG(price)` the average price > Write and run the inner query on its own first, like this. Then put it in brackets inside the outer one.

SELECT AVG(price) FROM pizzas

# Compare with a subquery - `price > (SELECT AVG(price) FROM pizzas)` more than the average The inner query returns one value, so it can go anywhere a number can. > A subquery used like this must return exactly one row and one column, or MySQL refuses.

SELECT name, price FROM pizzas
WHERE price > (
  SELECT AVG(price) FROM pizzas)

# IN with a subquery - `id IN (SELECT customer_id FROM orders WHERE pizza_id = 5)` the customer's id is in the list of people who ordered pizza 5 > A JOIN can often answer the same question. Use whichever reads more clearly.

SELECT name FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE pizza_id = 5)

# NOT IN: never did something - `NOT IN (SELECT customer_id FROM orders)` not in the list of customers who ordered Only George has never ordered. > This is the same question as the LEFT JOIN … IS NULL pattern from the JOINs level.

SELECT name FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders)

# The NOT IN trap Priya has no favourite, so the inner list contains a NULL. NOT IN against a list with a NULL is never true, so no rows come back. - `WHERE favourite_pizza_id IS NOT NULL` keeps the NULL out of the inner list With that added, four pizzas come back. > A classic interview question: "why does my NOT IN return nothing?" The answer is a NULL in the subquery.

SELECT name FROM pizzas
WHERE id NOT IN (
  SELECT favourite_pizza_id
  FROM staff)

Appian corner

Appian record queries have no subqueries. Run the first query into a!localVariables, then use its result as the value of a!queryFilter(operator: "in", …) in the second query. Or build a database view.

Practice questions

Try each one before opening the answer. To write and run them against the database in your browser, practise the level.

1. Which part runs first?
```
SELECT name FROM pizzas
WHERE price > (
  SELECT AVG(price) FROM pizzas)
```
- The outer SELECT name …
- The inner SELECT AVG(price) …
- Both at the same time
- Neither, it is an error
Show the answer
The inner SELECT AVG(price) …. The inner query runs first and its answer replaces the brackets.

2. Pizza names that cost more than the average.

SELECT name FROM pizzas
WHERE price > (
  SELECT ___(price) FROM pizzas)

Show the answer

SELECT name FROM pizzas
WHERE price > (
  SELECT AVG(price) FROM pizzas)

The average function from the counting club.

3. Names of the customers who ordered pizza 5 (Diavola). Use IN with a subquery.
Show the answer
```
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE pizza_id = 5)
```
WHERE id IN (SELECT customer_id FROM orders WHERE pizza_id = 5)
4. Customers who have never placed an order.
Show the answer
```
SELECT name
FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders)
```
NOT IN (SELECT customer_id FROM orders)
5. Pizzas that nobody has ever ordered. (Ace has a guess.)
Show the answer
```
SELECT name
FROM pizzas
WHERE id NOT IN (
  SELECT pizza_id FROM orders)
```
Same trick: id NOT IN (SELECT pizza_id FROM orders)

6. This should list the pizzas that are nobody’s favourite, but it returns nothing. Fix it.

SELECT name FROM pizzas
WHERE id NOT IN (
  SELECT favourite_pizza_id
  FROM staff)

Show the answer

SELECT name
FROM pizzas
WHERE id NOT IN (
  SELECT favourite_pizza_id
  FROM staff
  WHERE favourite_pizza_id IS NOT
    NULL)

Priya’s NULL is in the list. Add WHERE favourite_pizza_id IS NOT NULL inside the brackets.

7. Final boss! The names of customers who placed an order with a quantity above the average quantity. (A subquery inside a subquery.)
Show the answer
```
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE qty > (
    SELECT AVG(qty) FROM orders))
```
id IN (SELECT customer_id FROM orders WHERE qty > (SELECT AVG(qty) FROM orders))

Practise Subqueries