SQL subqueries are like little helpers that fetch data for our main query. They’re super handy when we need to filter our results based on some other data in our database.

Probably the most common placement of a subquery in SQL is in the WHERE clause. Placing a subquery in the WHERE clause allows us to compare a column against a set of rows that match a certain criteria.

Let’s look at three quick examples of subqueries in the WHERE clause.

Sample Data

First, let’s set up some sample tables to play with:

-- Create the customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

-- Create the products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- Create the orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Populate the customers table
INSERT INTO customers (customer_id, name, city)
VALUES 
    (1, 'John Smith', 'London'),
    (2, 'Jane Doe', 'Manchester'),
    (3, 'Bob Johnson', 'Liverpool'),
    (4, 'Alice Brown', 'London'),
    (5, 'Becky Black', 'Manchester');

-- Populate the products table
INSERT INTO products (product_id, name, price)
VALUES 
    (1, 'Widget', 10.99),
    (2, 'Gadget', 19.99),
    (3, 'Doohickey', 5.99),
    (4, 'Thingamajig', 15.99);

-- Populate the orders table
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date)
VALUES 
    (1, 1, 1, 2, '2024-07-01'),
    (2, 2, 2, 1, '2024-07-02'),
    (3, 3, 3, 3, '2024-07-03'),
    (4, 4, 4, 1, '2024-07-04'),
    (5, 1, 2, 1, '2024-07-05'),
    (6, 2, 3, 2, '2024-07-06');

Now that we’ve got our tables set up, let’s go ahead and run some queries.

Example 1: Finding Customers Who’ve Placed Orders

Let’s say we want to find all customers who have placed at least one order. We can use a subquery in the WHERE clause to check if a customer’s ID exists in the orders table:

SELECT name, city
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

Result:

name         city      
-----------  ----------
John Smith   London    
Jane Doe     Manchester
Bob Johnson  Liverpool 
Alice Brown  London    

This query returns the names and cities of all customers who have placed orders.

Example 2: Products with Above-Average Price

Now, let’s find all products that cost more than the average price:

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

Result:

name         price
-----------  -----
Gadget       19.99
Thingamajig  15.99

Here, the subquery uses the AVG() aggregate function to calculate the average price of all products, then the outer query uses that value to filter the query’s results to just those products that have a price greater than the amount returned by the subquery.

Example 3: Customers with Recent Orders

Finally, let’s find customers who’ve placed an order in the last 28 days:

SELECT name, city
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= DATEADD(day, -28, GETDATE())
);

Result:

name         city      
-----------  ----------
John Smith   London    
Jane Doe     Manchester
Alice Brown  London    

Here, the subquery finds all customer IDs with recent orders, and the main query returns the details of those customers.

Subqueries in Other Places

SQL subqueries can also be placed in other parts of the query (not just in the WHERE clause). For example, they can also be placed in the FROM clause, the HAVING clause, and even in the SELECT list.

See Understanding the SQL Subquery for examples SQL subqueries in the various clauses.