SQL Subquery Explained for Beginners (With Examples)

What is a SQL subquery? A subquery is a query written inside another query. The inner query runs first and hands its answer to the outer query. If you have ever thought "I need to find one value first, and then use it to filter my results," a subquery is the tool for that job.

This guide is for absolute beginners. We define every term, use one small example table, and explain each query line by line. You should already know basic SELECT and WHERE.

The example table we will use

Imagine a small shop. It has a table called products. A table is a grid of data. Each row is one product and each column is one fact about it.

CREATE TABLE products (
  id INT,
  name VARCHAR(50),
  category VARCHAR(50),
  price DECIMAL(6,2)
);

INSERT INTO products VALUES
  (1, 'Notebook', 'Paper', 4.50),
  (2, 'Pen', 'Writing', 1.20),
  (3, 'Backpack', 'Bags', 35.00),
  (4, 'Marker', 'Writing', 2.80),
  (5, 'Desk Lamp', 'Home', 22.00);

The first statement builds the table with four columns. The second adds five rows. Run both in any SQL tool, such as PostgreSQL's psql or MySQL.

The problem a subquery solves

Say you want every product that costs more than the average price. You need two steps. First, find the average. Second, list the products above it. Without a subquery you would run the average query, copy the number, and paste it into a second query. That breaks as soon as the data changes.

SELECT AVG(price) FROM products;

AVG is an aggregate function. It adds up all the prices and divides by the number of rows. Here the answer is 13.10. Now we let SQL do the copy-and-paste for us.

Your first subquery

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

Line 1 picks the columns we want to see. Line 2 says which table to read. Line 3 filters the rows. The part inside the brackets is the subquery. SQL runs it first, gets 13.10, and then treats line 3 as WHERE price > 13.10. The result is Backpack and Desk Lamp.

Two rules to remember: a subquery is always wrapped in round brackets, and it must return what the outer query expects. Here the outer query expects one single number, and AVG gives exactly one.

Subqueries that return a list: using IN

Sometimes the inner query returns many values instead of one. In that case use IN, which checks whether a value is in a list. Suppose we have a second table of categories that are on sale.

CREATE TABLE sale_categories (category VARCHAR(50));
INSERT INTO sale_categories VALUES ('Writing'), ('Bags');

SELECT name, category
FROM products
WHERE category IN (SELECT category FROM sale_categories);

The first line creates a one-column table. The INSERT adds two categories. The final query asks for products whose category appears in the list returned by the subquery. The result is Pen, Backpack and Marker. If you used = here instead of IN, you would get an error, because = can only compare against one value.

Subqueries in the FROM clause

You can also use a subquery as a temporary table. This is called a derived table. You must give it a name, called an alias.

SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS category_prices
WHERE avg_price > 3;

The inner query groups products by category and works out the average price of each. AS avg_price gives that column a friendly name. The outer query treats the result like a normal table called category_prices and keeps only the rows where avg_price is above 3. Most databases require the alias after the closing bracket, so do not forget it.

Subqueries in the SELECT list

SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Here the subquery sits inside the column list. For each product it shows how far the price is from the average. The subquery returns a single value, so SQL can subtract it from every row.

Where can a subquery go?

Subquery or JOIN?

A JOIN combines two tables side by side. A subquery asks a question and uses the answer. Many problems can be solved either way. As a beginner, pick whichever one you can read more easily. If you need columns from both tables in the result, use a JOIN. If you only need to filter by a calculated value or a list, a subquery is often simpler.

Common beginner mistakes

• Forgetting the round brackets around the inner query.
• Using = when the subquery returns many rows. Use IN instead.
• Returning more than one column in a subquery used in WHERE. Return exactly one column.
• Forgetting the alias on a subquery in FROM.
• Writing very deep nesting. If it gets hard to read, split the work into smaller steps.

Practice challenges

1. List products cheaper than the average price.
2. Find the most expensive product using WHERE price = (SELECT MAX(price) FROM products).
3. List products whose category is NOT IN the sale_categories table.

Try them one at a time and read the result before moving on. Changing a number in the table and re-running the query is a great way to see the subquery react.