SQL Subqueries Explained: Scalar, Correlated and EXISTS

Three common shapes

Scalar: (SELECT AVG(salary) FROM employees) returns a single number, usable anywhere a value is.

Correlated: the inner query mentions the outer row, as in WHERE e2.dept_id = e.dept_id, so it is evaluated for each outer row, giving a per-group comparison.

EXISTS: WHERE NOT EXISTS (SELECT 1 FROM … WHERE …) keeps rows that have no match, a clean way to write an anti-join.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

SELECT e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
)
ORDER BY e.dept_id, e.name;

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

Common mistakes

• NOT IN with NULLs. WHERE dept_id NOT IN (SELECT dept_id FROM employees) returns nothing at all because Zoya's dept_id is NULL. NOT EXISTS does not have this trap.
• Scalar subquery returning many rows. A subquery used with = or > must return one row. Aggregate it, or use IN / EXISTS.
• Nesting too deeply. Three or more nested levels are hard to read. Rewrite the steps as CTEs.

Practise Subqueries

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Customers Who Never Order

   Identify all customer records that have no matching entries in the Orders table.

   Easy✓ Solved
2. 2

   Second Highest Delivery Fee

   Return the second-highest distinct delivery fee, or NULL if there isn't one.

   Easy✓ Solved
3. 3

   YouTube: Breakout Videos Dominating Channels

   Find videos that account for at least 50% of their channel total views, calculating each video view percentage.

   Medium✓ Solved
4. 4

   Second Highest Salary

   Find the second highest distinct salary from the Employee table without duplicate values.

   Medium✓ Solved
5. 5

   Find Duplicate Rows to Remove (Keep Lowest ID)

   Formulate a query to eliminate duplicate email entries while preserving the record with smallest ID.

   Medium✓ Solved
6. 6

   Nth Highest Salary

   Write a dynamic MySQL stored procedure or subquery to retrieve the Nth distinct salary ranking.

   Medium✓ Solved

Want a full set? Work through the Subqueries & CTEs study plan.

Questions people ask

Are subqueries slower than joins?

Not necessarily. Modern MySQL and PostgreSQL optimisers often rewrite them into joins. Choose the form that states the question most clearly.

When should I use a CTE instead?

When a subquery is reused, or the logic has several steps. A CTE names each step and reads top to bottom.

Related concepts

• SQL CTE (WITH Clause) Explained with Examples
• SQL HAVING vs WHERE: What is the Difference?