SQL subqueries: types, EXISTS vs IN, and how they differ from joins

A subquery is a SELECT statement written inside another statement. It lets you answer a question in two steps without creating a temporary table: first "what is the average salary?", then "who earns more than that?". Subqueries are a favourite interview topic, mainly because of one question: how is a subquery different from a join, and when do you use which? This post answers that with working examples, runs on MySQL 8.4, and also covers the correlated form and a NULL trap that gives silently wrong answers.

The sample tables

CREATE TABLE departments (department_id INT PRIMARY KEY, department_name VARCHAR(30) NOT NULL);
CREATE TABLE employees (
  employee_id INT PRIMARY KEY, first_name VARCHAR(30), last_name VARCHAR(30),
  department_id INT NULL, manager_id INT NULL, salary INT);
INSERT INTO departments VALUES (101,'HR'),(102,'IT'),(103,'Sales'),(104,'Finance');
INSERT INTO employees VALUES
  (1,'John','Smith',101,NULL,50000), (2,'Jane','Johnson',102,1,70000),
  (3,'Robert','Williams',101,1,45000), (4,'Emily','Davis',103,1,52000),
  (5,'Alex','Brown',NULL,2,48000);

Alex has no department and Finance has no employees, as in the joins post.

What a subquery is

A subquery is wrapped in brackets, runs as part of the outer query, and produces a value or a set of rows that the outer query uses. By the shape of what it returns, there are four kinds.

1. Scalar subquery: one value

Returns one row with one column, so it can be used anywhere a single value fits:

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

+------------+--------+
| first_name | salary |
+------------+--------+
| Jane       |  70000 |
+------------+--------+

The inner query returns 53000.0000, and the outer query keeps rows above it. If the subquery returns more than one row where a single value is expected, MySQL stops:

SELECT first_name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department_id = 101);

ERROR 1242 (21000): Subquery returns more than 1 row

Use IN, ANY or ALL for multiple rows, or fix the query.

2. Multi-row subquery: a list of values

Used with IN, ANY, ALL or EXISTS:

SELECT first_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments
                        WHERE department_name IN ('HR', 'Sales'))
ORDER BY employee_id;

+------------+
| first_name |
+------------+
| John       |
| Robert     |
| Emily      |
+------------+

ALL compares with every value in the list. Who earns more than everyone in HR?

SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 101)
ORDER BY employee_id;

+------------+--------+
| first_name | salary |
+------------+--------+
| Jane       |  70000 |
| Emily      |  52000 |
+------------+--------+

3. Derived table: a subquery in FROM

A subquery in the FROM clause behaves like a temporary table and must have an alias. This is how you join to an aggregate:

SELECT d.department_name, t.total
FROM departments d
JOIN (SELECT department_id, SUM(salary) AS total
      FROM employees GROUP BY department_id) t
  ON t.department_id = d.department_id
ORDER BY d.department_id;

+-----------------+-------+
| department_name | total |
+-----------------+-------+
| HR              | 95000 |
| IT              | 70000 |
| Sales           | 52000 |
+-----------------+-------+

4. Correlated subquery: it uses the outer row

Every subquery above runs independently. A correlated subquery refers to a column of the outer query, so conceptually it runs once for each outer row. Who earns more than the average of their own department?

SELECT e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees x
                  WHERE x.department_id = e.department_id)
ORDER BY e.employee_id;

+------------+--------+---------------+
| first_name | salary | department_id |
+------------+--------+---------------+
| John       |  50000 |           101 |
+------------+--------+---------------+

HR averages 47,500, so John (50,000) qualifies. Jane, Emily and Alex are alone in their groups or have no group, so none exceeds their own average. Correlated subqueries are expressive, but they can be slow on big tables because of the repeated work. The optimiser can often rewrite them, and a join with a derived table or a window function (see below) is a common faster alternative.

Where a subquery can appear

Place	Example	Must return
WHERE / HAVING	salary > (SELECT AVG(...))	one value, or a list for IN
FROM	FROM (SELECT ...) t	a table (needs an alias)
SELECT list	(SELECT COUNT(*) ...) AS staff	exactly one value
INSERT, UPDATE, DELETE	... WHERE id IN (SELECT ...)	as needed

A subquery in the SELECT list is a scalar one, evaluated per row:

SELECT d.department_name,
       (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS staff
FROM departments d ORDER BY d.department_id;

+-----------------+-------+
| department_name | staff |
+-----------------+-------+
| HR              |     2 |
| IT              |     1 |
| Sales           |     1 |
| Finance         |     0 |
+-----------------+-------+

Finance shows 0, which an inner join with a GROUP BY would have lost.

IN versus EXISTS

Both test membership, but they think differently.

• IN compares a value with a list produced by the subquery.
• EXISTS asks whether the subquery returns at least one row; it can stop at the first match and does not care what the subquery selects (SELECT 1 is the convention).

Departments that have employees:

SELECT department_name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id)
ORDER BY department_id;

+-----------------+
| department_name |
+-----------------+
| HR              |
| IT              |
| Sales           |
+-----------------+

Modern MySQL turns both into similar execution plans for most queries, so choose for clarity and correctness. Use EXISTS for "is there any related row?", and IN for matching against a short list of values.

The NOT IN and NULL trap

Departments with no employees can be written two ways, and they are not equivalent when the subquery can return NULL:

-- NOT EXISTS: correct
SELECT department_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

+-----------------+
| department_name |
+-----------------+
| Finance         |
+-----------------+

-- NOT IN: returns nothing!
SELECT COUNT(*) FROM departments
WHERE department_id NOT IN (SELECT department_id FROM employees);

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Alex's department_id is NULL. x NOT IN (101, 102, 103, NULL) can never be true, because comparing with NULL gives "unknown". So NOT IN returns no rows at all the moment the list contains a single NULL. Fix it by filtering the NULLs out:

SELECT department_name FROM departments
WHERE department_id NOT IN (SELECT department_id FROM employees
                            WHERE department_id IS NOT NULL);

This returns Finance. The safest habit is to use NOT EXISTS (or a LEFT JOIN ... IS NULL) for "has no matching row".

Subquery versus join

Many questions can be written either way. The first query below uses a subquery and the second a join; both return John, Robert and Emily:

SELECT first_name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name IN ('HR','Sales'));

SELECT e.first_name FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name IN ('HR','Sales');

	Subquery	Join
What it does	computes a value or set, then uses it to filter or calculate	combines columns from several tables into one row
Columns from the second table in the result	no, only used for filtering	yes
Natural for	"is it in / above / has any" questions, aggregates as comparison values	reporting that shows data from several tables
Duplicates	cannot multiply the outer rows	can multiply rows if the match is one-to-many
Readability	step by step, good for layered logic	compact for simple matching
Performance	the optimiser often converts it to a join; correlated forms may run repeatedly	usually fast with indexes on the join columns

The practical rules: use a join when you need columns from both tables in the output. Use a subquery when you only need the other table to filter or to supply a single value, or when a join would multiply rows you do not want multiplied. Test both on your real data with EXPLAIN before assuming either is faster, and see performance optimisation for how.

Subqueries and the other tools

CTEs (common table expressions, WITH) give a subquery a name, which makes layered logic readable and lets you reuse it in the same statement:

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY department_id
)
SELECT e.first_name
FROM employees e JOIN dept_avg d USING (department_id)
WHERE e.salary > d.avg_salary
ORDER BY e.employee_id;

+------------+
| first_name |
+------------+
| John       |
+------------+

This returns the same answer as the correlated query, in a form that is easier to read and to check.

Window functions often replace correlated subqueries that rank or compare within groups, for example AVG(salary) OVER (PARTITION BY department_id) gives each row its department average without a subquery.

Row comparison matches pairs of columns at once. Highest earner per department:

SELECT first_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (
  SELECT department_id, MAX(salary) FROM employees
  WHERE department_id IS NOT NULL GROUP BY department_id)
ORDER BY employee_id;

+------------+---------------+--------+
| first_name | department_id | salary |
+------------+---------------+--------+
| John       |           101 |  50000 |
| Jane       |           102 |  70000 |
| Emily      |           103 |  52000 |
+------------+---------------+--------+

Common interview questions

• What is a subquery? A query nested inside another query's WHERE, FROM, SELECT list or DML statement.
• What is a correlated subquery? One that references columns of the outer query and is evaluated per outer row.
• IN or EXISTS? Prefer EXISTS for existence checks; beware NOT IN with NULL.
• Can a subquery return multiple columns? Yes, in FROM (as a table) or with row comparison; a scalar subquery must return one column.
• Which is faster, a join or a subquery? It depends on the data and indexes; check the query plan.

Common mistakes

• Using = with a subquery that can return several rows.
• Using NOT IN against a column that contains NULL.
• Forgetting the alias on a derived table.
• Writing a correlated subquery over a large table when a join with a grouped derived table would do.
• Selecting columns from a table that is only available inside the subquery.

Practice and next steps

Rewrite the "above their department average" query three ways (correlated subquery, derived table join and window function) and compare the results. Then read the joins post, the SQL joins cheat sheet and the MySQL interview questions. The MySQL course practises subqueries on a realistic shop database.