SQL Subqueries: Types, Examples and Common Mistakes

6
0

Introduction

In SQL, some queries require the result of another query to determine which records should be selected. For example, a company wants to list employees who earn more than the company average. The average is itself the answer to another query. A subquery lets SQL express both parts in one statement, so a filter can depend on a calculated value, a collection of values, or the presence of related records.

How Subqueries Work

A subquery is a query nested inside another SQL statement. In the examples below, it is a SELECT enclosed in parentheses. The surrounding statement is called the outer query, and the nested query is the inner query.

The result needed by the outer query determines which form is appropriate.

Form

Result shape

Typical purpose

Scalar subquery

One column and at most one row

Supply a single comparison or output value

Column subquery

One column with zero or more rows

Provide values for IN, ANY, or ALL

Row subquery

Several columns and at most one row

Compare a combination of values

Table subquery

Zero or more rows with one or more columns

Supply a result that can be queried in FROM

Subqueries can also be grouped by whether they rely on the main query. An independent subquery stands on its own, while a correlated subquery depends on data from the outer query. These aren't strict categories, though. For example, a correlated subquery can still return just a single value.

Reading an independent inner query first can help explain the result. However, this is a logical explanation, not a fixed execution schedule. The database optimizer decides how to execute the statement.

A Scalar Subquery in Practice

The examples below use the following two tables. Each department and employee has a unique ID. An employee can belong to one department or remain unassigned. All salaries are known; Isha’s NULL department_id means that she has no assigned department. Amounts below omit trailing decimal zeros.

departments

department_id

department_name

10

Engineering

20

Support

30

Design

employees

employee_id

employee_name

department_id

salary

101

Mira

10

60000

102

Kabir

10

80000

103

Tara

20

40000

104

Dev

20

50000

105

Isha

NULL

30000

Find employees earning above the company average

The inner query calculates the average salary. The outer query compares each employee’s salary with that single value.

SELECT e.employee_id, e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY e.employee_id;

Output

employee_id

employee_name

salary

101

Mira

60000

102

Kabir

80000

The five salaries total 260000, so their average is 52000. Mira and Kabir earn more than 52000. The other employees do not satisfy the condition.

A Single Comparison Value

A Single Comparison Value

The subquery is scalar because it supplies one value. It is also independent because it does not refer to the outer alias e.

Checking Membership and Missing Relationships

IN checks membership

To find departments that have employees, check whether each department ID appears among the employee department IDs.

SELECT d.department_id, d.department_name
FROM departments AS d
WHERE d.department_id IN (
SELECT e.department_id
FROM employees AS e
)
ORDER BY d.department_id;

Output

department_id

department_name

10

Engineering

20

Support

The inner result contains 10, 10, 20, 20, and NULL. IDs 10 and 20 have matches. Repeated values inside IN do not multiply outer rows, so DISTINCT is unnecessary in this subquery.

EXISTS checks for a qualifying row

The same question can be expressed by checking whether at least one employee belongs to the current department.

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

This returns the same two departments, Engineering and Support. SELECT 1 is a conventional placeholder: EXISTS checks whether a qualifying row is present. It does not need that employee’s name or salary.

The condition e.department_id = d.department_id links an inner employee row to an outer department row. That outer reference makes this a correlated subquery. Multiple matching employees still produce only one output row for each department in this query.

NOT EXISTS finds missing relationships

To find departments without employees, keep a department only when the inner query has no matching employee.

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

Output

department_id

department_name

30

Design

Design has no matching employee. Isha’s unassigned department does not count as a match for department 30.

Why NOT IN can behave differently

It may seem that replacing IN with NOT IN would produce the same missing-department result. With this dataset, it does not.

SELECT d.department_id, d.department_name
FROM departments AS d
WHERE d.department_id NOT IN (
SELECT e.department_id
FROM employees AS e
)
ORDER BY d.department_id;

Output

No rows.

The inner result includes NULL. SQL cannot establish that 30 differs from an unknown department ID, so the NOT IN condition for Design is UNKNOWN, rather than TRUE. WHERE keeps only rows for which its condition is TRUE.

Department ID

NOT IN result

Reason

10

FALSE

A matching 10 exists

20

FALSE

A matching 20 exists

30

UNKNOWN

No equal value exists, but NULL is present

NOT EXISTS avoids this particular trap by checking matching rows directly. Its equality condition still follows normal NULL rules. When using NOT IN, account for possible NULLs on both sides of the comparison.

Correlated Subqueries

Now change the question: which employees earn more than the average salary in their own department? The comparison value depends on the current employee’s department.

SELECT e.employee_id, e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.employee_id;

Output

employee_id

employee_name

salary

102

Kabir

80000

104

Dev

50000

The alias e identifies the employee being considered. The alias e2 identifies the employees used to calculate the department average. The condition e2.department_id = e.department_id restricts that average to the current employee’s department.

Engineering’s average is 70000, so Kabir qualifies. Support’s average is 45000, so Dev qualifies. Isha’s NULL department does not match through ordinary equality; her correlated AVG is NULL, and the salary comparison is not TRUE.

Use Each Row's Group

Use Each Row's Group

This row-based interpretation explains the dependency. It does not guarantee that the database physically executes the inner query separately for every outer row.

Other Forms and Practical Rules

A subquery in FROM creates a derived table

A derived table is a query result used as a table within another query. Here, the inner query produces department averages, and the outer query keeps averages above 50000.

SELECT department_averages.department_id,
department_averages.avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) AS department_averages
WHERE department_averages.avg_salary > 50000
ORDER BY department_averages.department_id;

Output

department_id

avg_salary

10

70000

The name department_averages identifies the result available to the outer query. Its avg_salary column holds each department’s calculated average. Engineering’s 70000 passes the filter; Support’s 45000 does not. The query does not create a permanent table.

Subqueries can also appear in the SELECT list to supply a scalar output value, or in HAVING to filter grouped results. They may be nested further or used in data-changing statements such as INSERT, UPDATE, and DELETE, subject to the database’s rules.

ANY and ALL compare against several values

Support salaries are 40000 and 50000. These values make the difference between ANY and ALL clear:

Operator

Meaning

Comparison using 50000

> ANY

Greater than at least one returned value

TRUE, because 50000 > 40000

> ALL

Greater than every returned value

FALSE, because 50000 is not greater than 50000

SOME is a synonym for ANY, and IN is equivalent to = ANY. If the subquery returns no rows, ANY is FALSE and ALL is TRUE. NULL comparisons can produce UNKNOWN when there is no decisive TRUE for ANY or decisive FALSE for ALL.

Avoid common mistakes

  • Respect scalar row counts: A scalar position requires one column and at most one row. Zero rows give NULL; multiple rows cause an error. An aggregate such as AVG without GROUP BY returns one row, containing NULL when no values qualify. Do not add LIMIT 1 merely to hide an unintended multiple-row result.

  • Make outer references explicit: Use clear aliases in correlated conditions. A mistaken inner-to-inner comparison can remove the intended relationship to the outer row and change the answer.

  • Choose joins according to the result needed: A join is useful when the output needs columns from matching records. Multiple matches can repeat an outer row. EXISTS expresses a presence check without adding one output row per match.

  • Check performance with evidence: IN, EXISTS, and joins have no universal speed ranking. Equivalent forms may receive similar execution plans. Use EXPLAIN and consider the actual data, filters, and indexes when tuning a query.

Summary

A subquery lets one SQL statement use the result of another query. Scalar subqueries supply a single value, IN checks membership, and EXISTS or NOT EXISTS tests for matching rows. Correlated subqueries refer to an outer row, while derived tables make a query result available through FROM. Choose a form that matches the question, check row counts and NULL behavior, and distinguish logical reasoning from the execution plan chosen by the database.

CS Core

Read Similar Blogs

Comments0