Subqueries — queries inside queries

A subquery is a SELECT statement nested inside another query. It can appear in WHERE, FROM, or SELECT.

Scalar subquery (returns one value)

Returns a single value and can be used with =, >, <, etc.:

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

IN subquery (returns a list)

Returns a column of values; the outer query checks membership:

-- Products that have been ordered at least once
SELECT name
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items);

NOT IN subquery

-- Products never ordered
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

Caution: NOT IN returns no rows if the subquery contains any NULL. Prefer NOT EXISTS when NULLs are possible.

EXISTS subquery

Returns TRUE if the subquery returns at least one row:

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

Derived table (subquery in FROM)

A subquery in FROM acts like a temporary table:

SELECT dept, avg_sal
FROM (
  SELECT department_id AS dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_stats
WHERE avg_sal > 8000;

Tips

• Scalar subqueries that return more than one row will error — use LIMIT 1 or aggregate if unsure.
• For complex derived tables, a CTE (WITH) is often more readable.