SQL Subqueries: Correlated vs Non-Correlated, Scalar, and EXISTS Explained

Subqueries are one of those SQL concepts that look simple in a tutorial but trip people up badly in interviews.

The reason: there are several distinct types of subqueries, each used differently, and candidates often mix them up or default to the wrong one. This guide breaks down every type clearly.

1. What is a subquery?

A subquery is a SELECT statement nested inside another SQL statement.

sql
SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id    -- this is the subquery
  FROM orders
  WHERE amount > 1000
);

The inner query runs first, produces a result, and the outer query uses that result.

Subqueries can live in three places:

• The WHERE clause — filtering based on another query
• The FROM clause — as an inline table (also called a derived table)
• The SELECT clause — as a scalar value

2. Non-correlated subqueries — independent inner query

A non-correlated subquery runs once and independently of the outer query. The inner query has no reference to the outer query.

sql
-- Non-correlated: inner query runs ONCE
SELECT product_id, name, price
FROM products
WHERE price > (
  SELECT AVG(price)   -- runs once, returns one number
  FROM products
);

The subquery SELECT AVG(price) FROM products runs once, returns a single value (say, 45.00), and then the outer query filters all products with price > 45.00.

Non-correlated subqueries are efficient and straightforward.

3. Correlated subqueries — references the outer query

A correlated subquery references a column from the outer query. It runs once per row of the outer query.

sql
-- Correlated: inner query runs ONCE PER ROW
SELECT
  e.employee_id,
  e.name,
  e.salary,
  e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id  -- references outer query's e.department_id
);

For each employee row, the subquery calculates the average salary for THAT employee's department. The e.department_id reference ties the inner query to the current outer row.

Performance note: correlated subqueries can be slow on large tables because they run once per row. A JOIN or window function is often faster — but correlated subqueries are cleaner to read and perfectly acceptable in interviews.

4. Scalar subqueries — return a single value

A scalar subquery returns exactly one row and one column. It can appear anywhere a single value is expected — including in SELECT.

sql
-- Scalar subquery in SELECT
SELECT
  product_id,
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

sql
-- Scalar subquery in WHERE
SELECT name
FROM employees
WHERE department_id = (
  SELECT id
  FROM departments
  WHERE name = 'Engineering'
);

If a scalar subquery returns more than one row, it throws an error. Always be certain it returns at most one row — use LIMIT 1 or aggregate functions to guarantee this.

5. Subqueries in the FROM clause — derived tables

A subquery in FROM acts as a temporary table for the outer query.

sql
SELECT
  country,
  avg_spend
FROM (
  SELECT
    c.country,
    AVG(o.amount) AS avg_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.country
) AS country_averages
WHERE avg_spend > 500
ORDER BY avg_spend DESC;

You must alias derived tables (here: country_averages). This is equivalent to a CTE and often interchangeable — use whichever is more readable.

6. EXISTS vs IN — an important interview distinction

Both EXISTS and IN can answer "does a matching row exist?" but they work differently.

IN with a subquery:

sql
SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE amount > 1000
);

EXISTS:

sql
SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.amount > 1000
);

Key differences:

Interview tip: if the subquery could return NULLs, prefer EXISTS. Also mention that EXISTS short-circuits — it stops checking as soon as one matching row is found.

7. NOT EXISTS — the anti-join alternative

"Find customers who have never placed an order."

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

This is cleaner than NOT IN when NULLs might be present in the subquery result. NOT IN with any NULL in the subquery returns zero rows — a common silent bug.

sql
-- Dangerous if orders.customer_id has any NULLs:
WHERE customer_id NOT IN (SELECT customer_id FROM orders)

-- Safe alternative:
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id)

8. Subqueries vs CTEs vs JOINs — when to use each

9. Common interview patterns

Find the second highest salary:

sql
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Find employees earning more than their manager:

sql
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT m.salary
  FROM employees m
  WHERE m.employee_id = e.manager_id
);

Find products never ordered:

sql
SELECT product_id, name
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id
  FROM order_items
  WHERE product_id IS NOT NULL
);

10. Practice subquery problems on TheQueryLab

Subqueries appear in a wide range of interview problems — from simple filters to complex multi-step analytics. The best way to get comfortable is to practice on realistic schemas where the subquery is genuinely necessary.

The SQL Interview Preparation Kit on TheQueryLab includes problems specifically designed to test subquery understanding — including correlated subqueries, EXISTS patterns, and scalar subqueries in SELECT.

Once you can write any subquery type instinctively, SQL interviews become significantly easier.