SQL Subqueries Tutorial: Types, Correlated Subqueries and EXISTS With Examples

Some questions take two steps to answer. “Which orders are above average?” requires you to find the average first, and then compare each order with it. SQL subqueries let you write both steps as one statement.

This tutorial covers each type using two small tables. We ran these queries in SQLite 3.50, and the results shown come from that run. Standard subquery syntax is widely supported, and we point out the places where databases differ.

What is a subquery?

A subquery, also called an inner or nested query, is a SELECT statement placed inside another statement, in round brackets. Conceptually, the inner query produces a result that the outer query then uses.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

The sample tables

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Note that Dev has no orders. That detail matters for several examples below.

Types of subquery at a glance

Scalar subqueries

A scalar subquery returns exactly one value, so you can use it wherever a single value makes sense. Here is the query from earlier, with its result. The average of all orders is 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

You could not write WHERE total > AVG(total), because aggregates are not allowed directly in WHERE. The subquery gets around this, by calculating the value separately.

What if a “scalar” subquery returns several rows? Most databases, including PostgreSQL, MySQL and SQL Server, raise an error. SQLite does not. In our test, it silently used only the first row, which could hide a real bug, so do not depend on this behaviour.

IN and NOT IN

When the inner query returns a list, use IN. This finds customers with at least one order over 1000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN does the opposite, finding customers who have never ordered:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

That works here, but NOT IN has a dangerous edge case, which we cover below.

Subqueries in FROM

A subquery in FROM acts as a temporary table, often called a derived table. It is useful when you need to aggregate twice, as in this query for the average spending per customer in each city:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

The inner query totals each customer, and the outer one averages those totals. Giving the derived table an alias is a good habit, as some databases require it. If GROUP BY is new to you, see SQL GROUP BY and HAVING.

Subqueries in SELECT

A scalar subquery can also add a calculated column. This one counts orders per customer:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev appears with zero. An inner join would have dropped Dev completely, because Dev has no matching rows. You could get the same result with a LEFT JOIN and GROUP BY, which we explain in SQL joins explained.

Correlated subqueries

The previous example is also correlated, since the inner query refers to c.id from the outer query. A non-correlated subquery can run alone. A correlated one cannot, because it depends on a value from the current outer row.

This finds orders above that same customer’s own average:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Here is how to read it. For order 101, the inner query averages customer 1’s orders (1200 and 800), which gives 1000. Since 1200 is greater, the row is kept. For order 102, the same average applies, but 800 is not greater, so it is dropped. Customer 2’s single order equals its own average, so it is dropped too.

“Once per row” describes the logic, and query optimizers often rewrite these into something far more efficient. Still, a slow correlated subquery is worth investigating. An index on the inner column, here orders.customer_id, can make a major difference. See SQL indexes explained.

EXISTS and NOT EXISTS

EXISTS asks a yes or no question: does the inner query return at least one row? It can stop at the first match, and what you select inside does not matter, which is why people often write SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

The NOT IN NULL trap

This is one of the most common subquery bugs. We created a table with one real ID and one NULL, then ran both forms against it:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Why does the first return nothing? id NOT IN (1, NULL) means id <> 1 AND id <> NULL. Any comparison with NULL is unknown rather than true, so the whole condition can never be true. There is no error message and no warning, just an empty result that looks plausible.

Subquery vs JOIN vs CTE

Many queries can be written more than one way. Earlier we found customers with a large order using IN. Here is the same result with a join:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Notice the DISTINCT. A join can produce duplicates if a customer has several matches, whereas IN and EXISTS cannot.

Performance varies by database and data, and modern optimizers often produce similar plans. So choose the clearest form first, then check EXPLAIN if it turns out to be slow.

Common mistakes

• Using = with a multi-row result. Use IN, or make sure the inner query returns one row.
• Using NOT IN on a nullable column. You can silently lose every row.
• Using unclear aliases. In correlated queries, qualify every column, or it may bind to the wrong table.
• Deep nesting. Three levels are hard to read, so convert them to CTEs.
• Adding ORDER BY inside. It usually has no effect on the outer result, and some databases reject it.

Final thoughts

Subqueries are a natural way to express two-step questions. Start with scalar subqueries and IN, then practise correlated ones by reasoning through a single outer row at a time. And remember NOT EXISTS whenever NULLs are possible. To practise, write one question three ways, with a subquery, a join and a CTE, and compare both results and readability.

Frequently asked questions

Keep learning