Subqueries: Queries Inside Queries

A subquery is a SELECT statement nested inside another query. It lets you use the result of one query as an input to another.

Scalar subqueries (returning a single value)

Used anywhere a single value is expected — often after a comparison operator:

-- Employees earning more than the company average
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subqueries with IN

When the subquery returns a list of values:

-- Customers who have placed at least one order
SELECT customer_name
FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

Subqueries with NOT IN

-- Customers who have never ordered
SELECT customer_name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Careful: if the subquery could return a NULL, NOT IN can unexpectedly return zero rows for the whole query. Filtering out NULLs first (as above) or using NOT EXISTS avoids this trap.

EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns any rows at all — it doesn't care about the actual values, just whether a match exists. This is often the most efficient way to check "is there a related row?":

-- Same result as the IN example above, often faster
SELECT customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Correlated subqueries

A subquery that references the outer query (like the EXISTS example above, which uses c.id from the outer query) is called correlated — it runs conceptually once per outer row, rather than once overall.

-- Each employee compared to their OWN department's average
SELECT first_name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id
);

Subqueries in the FROM clause

You can also use a subquery as if it were a table — this is sometimes called a derived table:

SELECT department_id, avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) AS department_averages
WHERE avg_salary > 100000;

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;