← SQL roadmap

Topic 21 of 52 · Intermediate

Subqueries & EXISTS

Queries inside queries: scalar, IN, EXISTS and correlated.

Concept

A subquery is a SELECT nested inside another statement. A scalar subquery returns one value; others return a list or a table.

A correlated subquery references the outer row and runs once per outer row — powerful but can be slow.

EXISTS stops at the first match and handles NULLs safely. NOT IN breaks if the subquery returns any NULL, so prefer NOT EXISTS.

Code (SQL · MySQL 8)

-- Above-average orders
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Highest paid per department (correlated)
SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.dept_id = e.dept_id);

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

Remember

  • Prefer NOT EXISTS over NOT IN.
  • Correlated subqueries can often be rewritten as JOINs or windows.
  • Derived tables (subquery in FROM) need an alias.

Interview questions

Find the second-highest salary.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use DENSE_RANK() = 2.

Why is NOT IN dangerous?

If the subquery contains a NULL, x NOT IN (...) is UNKNOWN for every row, so nothing is returned.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Subqueries & EXISTS” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.