SQL Subqueries

A SQL subquery is a query inside another query. The inner query runs first (logically) and its result is used by the outer query, which lets you answer questions in steps, such as "products priced above the average price".

Subquery in WHERE

The inner query calculates the average price; the outer query keeps products above it.

SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
ORDER BY Price DESC;

ProductName  | Price
-------------+------
Coffee Beans | 22
Olive Oil    | 18.75
Almonds      | 15
Basmati Rice | 12.5
(4 rows)

Subquery in FROM (Derived Table)

A subquery in FROM acts like a temporary table. Here we total units per customer, then keep only the top buyers.

SELECT t.CustomerID, t.units
FROM (
  SELECT CustomerID, SUM(Quantity) AS units
  FROM Orders
  GROUP BY CustomerID
) AS t
WHERE t.units >= 20
ORDER BY t.units DESC;

CustomerID | units
-----------+------
5          | 40
2          | 33
7          | 30
(3 rows)

Correlated Subquery

A correlated subquery refers to the outer row, so it runs once per row. This shows each customer with their number of orders.

SELECT c.CustomerName,
       (SELECT COUNT(*) FROM Orders o
        WHERE o.CustomerID = c.CustomerID) AS order_count
FROM Customers c
ORDER BY order_count DESC, c.CustomerName
LIMIT 4;

CustomerName   | order_count
---------------+------------
Alpha Traders  | 2
Blue Sky Foods | 2
Cedar Market   | 2
Evergreen Co   | 1
(4 rows)

EXISTS and NOT EXISTS

EXISTS is true if the subquery returns at least one row. NOT EXISTS is the safe way to find "rows without a match" because it handles NULLs correctly.

SELECT ProductName
FROM Products p
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.ProductID = p.ProductID
);

ProductName
-----------
Honey
(1 row)

Frequently asked questions

Is a subquery or a JOIN faster?

Modern optimizers often turn them into the same plan. Choose the form that is clearest; check the execution plan if performance matters.

What is a correlated subquery?

A subquery that refers to a column of the outer query. It is evaluated for each outer row, so it can be slower on big tables.

Can a subquery return more than one row?

Yes, when used with IN, EXISTS, ANY/ALL or in FROM. A subquery used with = or > must return a single value.