SQL Subqueries

A subquery is a query inside another query. The inner query runs first and hands its result to the outer query. Subqueries let you answer questions in steps, like "which products cost more than the average product?" when you don't know the average in advance.

Where subqueries can go

Syntax

SELECT ...
FROM table_name
WHERE column > (SELECT AGGREGATE(column) FROM table_name);

Examples

1. Scalar subquery in WHERE

Products priced above the average price:

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

Result:

(3 rows)

The subquery returns one number (the average, about ₹5,465), and the outer query compares every row to it.

2. Scalar subquery in SELECT

Show how each product's price compares with the average:

SELECT product_name,
       price,
       ROUND((SELECT AVG(price) FROM products), 0) AS avg_price,
       ROUND(price - (SELECT AVG(price) FROM products), 0) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC
LIMIT 4;

Result:

(4 rows)

3. List subquery with IN

Customers who have bought something from the Electronics category:

SELECT customer_name, city
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p     ON p.product_id = oi.product_id
  WHERE p.category_id = 1
)
ORDER BY customer_name;

Result:

(5 rows)

Using IN instead of a join means each customer appears once, however many Electronics items they bought.

4. NOT IN

Customers who have never placed an order:

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Result:

(1 row)

This is safe here because orders.customer_id never contains NULL. If it might, use NOT EXISTS instead.

5. Subquery in FROM (derived table)

Aggregate first, then work with the result like a table. Average order value per customer, keeping only customers above ₹10,000:

SELECT customer_id, orders, ROUND(avg_order_value, 2) AS avg_order_value
FROM (
  SELECT o.customer_id,
         COUNT(DISTINCT o.order_id) AS orders,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) / COUNT(DISTINCT o.order_id) AS avg_order_value
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY o.customer_id
) AS customer_stats
WHERE avg_order_value > 10000
ORDER BY avg_order_value DESC;

Result:

(2 rows)

6. Which product is the most expensive?

The question aggregate functions alone couldn't answer in lesson 13:

SELECT product_name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Result:

(1 row)

If two products tied for the top price, both would be returned, unlike ORDER BY price DESC LIMIT 1.

Common mistakes

A scalar subquery that returns more than one row:

SELECT product_name
FROM products
WHERE price > (SELECT price FROM products WHERE category_id = 1);

Error: more than one row returned by a subquery used as an expression

Use an aggregate (MAX, AVG) to get one value, or > ALL (...) / > ANY (...).

• NOT IN with possible NULLs in the subquery, which returns no rows.
• Deeply nested subqueries. Three levels deep is hard to read. Rewrite with CTEs.

Practice question

List orders whose total quantity of items is greater than the average quantity per order line.

SELECT order_id, SUM(quantity) AS total_qty
FROM order_items
GROUP BY order_id
HAVING SUM(quantity) > (SELECT AVG(quantity) FROM order_items)
ORDER BY total_qty DESC
LIMIT 6;

Key takeaways

• A subquery is a query in brackets inside another query.
• Scalar subqueries return one value; IN subqueries return a list; FROM subqueries return a table.
• Derived tables let you aggregate first and filter or join afterwards.
• For readability, prefer CTEs once queries get long.