SQL Subqueries in WHERE, SELECT, FROM

Subqueries in WHERE, SELECT and FROM

A subquery is a query in brackets inside another query. Its result is used like a value, a list or a table, depending on where you put it.

The subquery is also called the inner query, and the query around it is the outer (or main) query.

Syntax

Order of execution: the subquery runs first, once, and hands its result to the outer query, which then runs as usual: FROM → WHERE → GROUP BY → SELECT → ORDER BY

Code
SELECT column, (SELECT AGG(column) FROM table_b) AS alias        -- one value
FROM (SELECT column FROM table_a WHERE condition) AS t           -- a whole table
WHERE column > (SELECT AGG(column) FROM table_b)                 -- one value
  AND column IN (SELECT column FROM table_c WHERE condition);    -- a list

Where	The subquery must return
`WHERE x > (…)`, `SELECT (…)`, `HAVING AGG(x) > (…)`	one value: one row, one column (a scalar subquery)
`WHERE x IN (…)`	one column, any number of rows (a multi-row subquery)
`WHERE (x, y) IN (…)`	the same number of columns as the brackets on the left, any number of rows
`FROM (…) AS t`	any table; give it a name (Oracle writes the name without `AS`)

Examples

One value in WHERE

Compare each row with a value worked out from the whole table. Typing the average in by hand goes stale as soon as the orders change; the subquery works it out again on every run.

Code
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC
LIMIT 5;
 
-- Fails: an aggregate can't sit directly in WHERE
SELECT order_id FROM orders WHERE total_amount > AVG(total_amount);

One value in SELECT

Show the same summary value next to every row.

Code
SELECT product_name, rating, (SELECT MAX(rating) FROM products) AS best_rating
FROM products
WHERE category_id = 2
ORDER BY product_id;
 
-- Fails: the subquery returns many rows where one value is needed
SELECT product_name, (SELECT rating FROM products) FROM products;

One value in HAVING

Compare each group with a value worked out from the whole table.

Code
-- Categories whose average price beats the average price of all products
SELECT category_id, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > (SELECT AVG(price) FROM products)
ORDER BY category_id;

A list with IN

IN checks membership, so each outer row appears once however often it matches.

Code
SELECT full_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM reviews WHERE rating = 5)
ORDER BY full_name;
 
-- The list can come from a grouped query: products with at least 2 reviews
SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM reviews GROUP BY product_id HAVING COUNT(*) >= 2)
ORDER BY product_name;

Several columns with IN

Match a pair of values at once, such as the biggest order in each city.

Code
-- City and amount must both match one row of the subquery; a tie keeps both orders
-- (SQL Server doesn't allow several columns before IN; use EXISTS there)
SELECT shipping_city, order_id, total_amount
FROM orders
WHERE (shipping_city, total_amount) IN (SELECT shipping_city, MAX(total_amount)
                                        FROM orders
                                        GROUP BY shipping_city)
ORDER BY shipping_city;

NOT IN and NULLs

If the list contains a NULL, NOT IN is never true, so no rows come back.

Code
-- 0 rows: parent_category_id is NULL for the top category
SELECT category_name FROM categories
WHERE category_id NOT IN (SELECT parent_category_id FROM categories);
 
SELECT category_name FROM categories
WHERE category_id NOT IN (SELECT parent_category_id FROM categories
                          WHERE parent_category_id IS NOT NULL)
ORDER BY category_name;

A table in FROM

Summarise once, then query the summary like a table.

Code
SELECT ROUND(AVG(order_count), 2) AS avg_orders_per_customer
FROM (SELECT customer_id, COUNT(*) AS order_count
      FROM orders
      GROUP BY customer_id) AS per_customer;

In UPDATE and DELETE

Choose the rows to change with a subquery, just like in a SELECT.

Code
-- Put the products nobody has ordered on a 10% sale
UPDATE products SET price = price * 0.9
WHERE product_id NOT IN (SELECT product_id FROM order_items);
 
-- Delete the reviews of products that are out of stock
DELETE FROM reviews
WHERE product_id IN (SELECT product_id FROM products WHERE stock = 0);

Remember

A subquery used as a value must return exactly one row and one column.
Aggregates can't go directly in WHERE; compute them in a subquery.
IN never repeats outer rows, unlike a join.
NOT IN returns nothing when the list has a NULL; filter the NULLs out.

Practice

Subqueries in WHERE, SELECT and FROM

A subquery is a query in brackets inside another query. Its result is used like a value, a list or a table, depending on where you put it.

The subquery is also called the inner query, and the query around it is the outer (or main) query.

Syntax

Order of execution: the subquery runs first, once, and hands its result to the outer query, which then runs as usual: FROM → WHERE → GROUP BY → SELECT → ORDER BY

Code
SELECT column, (SELECT AGG(column) FROM table_b) AS alias        -- one value
FROM (SELECT column FROM table_a WHERE condition) AS t           -- a whole table
WHERE column > (SELECT AGG(column) FROM table_b)                 -- one value
  AND column IN (SELECT column FROM table_c WHERE condition);    -- a list

Where	The subquery must return
`WHERE x > (…)`, `SELECT (…)`, `HAVING AGG(x) > (…)`	one value: one row, one column (a scalar subquery)
`WHERE x IN (…)`	one column, any number of rows (a multi-row subquery)
`WHERE (x, y) IN (…)`	the same number of columns as the brackets on the left, any number of rows
`FROM (…) AS t`	any table; give it a name (Oracle writes the name without `AS`)

Examples

One value in WHERE

Compare each row with a value worked out from the whole table. Typing the average in by hand goes stale as soon as the orders change; the subquery works it out again on every run.

Code
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC
LIMIT 5;
 
-- Fails: an aggregate can't sit directly in WHERE
SELECT order_id FROM orders WHERE total_amount > AVG(total_amount);

One value in SELECT

Show the same summary value next to every row.

Code
SELECT product_name, rating, (SELECT MAX(rating) FROM products) AS best_rating
FROM products
WHERE category_id = 2
ORDER BY product_id;
 
-- Fails: the subquery returns many rows where one value is needed
SELECT product_name, (SELECT rating FROM products) FROM products;

One value in HAVING

Compare each group with a value worked out from the whole table.

Code
-- Categories whose average price beats the average price of all products
SELECT category_id, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > (SELECT AVG(price) FROM products)
ORDER BY category_id;

A list with IN

IN checks membership, so each outer row appears once however often it matches.

Code
SELECT full_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM reviews WHERE rating = 5)
ORDER BY full_name;
 
-- The list can come from a grouped query: products with at least 2 reviews
SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM reviews GROUP BY product_id HAVING COUNT(*) >= 2)
ORDER BY product_name;

Several columns with IN

Match a pair of values at once, such as the biggest order in each city.

Code
-- City and amount must both match one row of the subquery; a tie keeps both orders
-- (SQL Server doesn't allow several columns before IN; use EXISTS there)
SELECT shipping_city, order_id, total_amount
FROM orders
WHERE (shipping_city, total_amount) IN (SELECT shipping_city, MAX(total_amount)
                                        FROM orders
                                        GROUP BY shipping_city)
ORDER BY shipping_city;

NOT IN and NULLs

If the list contains a NULL, NOT IN is never true, so no rows come back.

Code
-- 0 rows: parent_category_id is NULL for the top category
SELECT category_name FROM categories
WHERE category_id NOT IN (SELECT parent_category_id FROM categories);
 
SELECT category_name FROM categories
WHERE category_id NOT IN (SELECT parent_category_id FROM categories
                          WHERE parent_category_id IS NOT NULL)
ORDER BY category_name;

A table in FROM

Summarise once, then query the summary like a table.

Code
SELECT ROUND(AVG(order_count), 2) AS avg_orders_per_customer
FROM (SELECT customer_id, COUNT(*) AS order_count
      FROM orders
      GROUP BY customer_id) AS per_customer;

In UPDATE and DELETE

Choose the rows to change with a subquery, just like in a SELECT.

Code
-- Put the products nobody has ordered on a 10% sale
UPDATE products SET price = price * 0.9
WHERE product_id NOT IN (SELECT product_id FROM order_items);
 
-- Delete the reviews of products that are out of stock
DELETE FROM reviews
WHERE product_id IN (SELECT product_id FROM products WHERE stock = 0);

Remember

A subquery used as a value must return exactly one row and one column.
Aggregates can't go directly in WHERE; compute them in a subquery.
IN never repeats outer rows, unlike a join.
NOT IN returns nothing when the list has a NULL; filter the NULLs out.

SQLAsking Questions Within Questions

Syntax

Examples

One value in WHERE

One value in SELECT

One value in HAVING

A list with IN

Several columns with IN

NOT IN and NULLs

A table in FROM

In UPDATE and DELETE

Remember

Practice

SQLAsking Questions Within Questions

Syntax

Examples

One value in WHERE

One value in SELECT

One value in HAVING

A list with IN

Several columns with IN

NOT IN and NULLs

A table in FROM

In UPDATE and DELETE

Remember

Practice