KAHIBARO
10.12. Subqueries
Table of Contents
Understanding Subqueries
Subqueries let you place one query inside another. They are a powerful way to break complex problems into smaller pieces and let the database do the combining for you.
In this chapter you will learn what subqueries are, where you can use them, and how to read and write them confidently.
Key idea: A subquery is a SELECT statement used inside another SQL statement.
It must return data in a shape that matches where it is used, for example a single value, a single column, or a table.
What Is a Subquery?
A subquery is a query inside another query. It is also called an inner query or nested query. The outer query uses the result of the subquery as if it was a value, a list, or a virtual table.
Basic form:
SELECT ...
FROM ...
WHERE some_column = (
SELECT ...
FROM ...
WHERE ...
);You can put subqueries in many places:
| Place | Example shape |
|---|---|
WHERE | Compare against a value or list |
FROM | Use as a virtual table (derived table) |
SELECT list | Compute a value per row |
HAVING | Filter groups using another query |
Simple Example
Suppose you have two tables:
employees(id, name, department_id, salary)
departments(id, name)Get all employees who work in the "Sales" department:
SELECT *
FROM employees
WHERE department_id = (
SELECT id
FROM departments
WHERE name = 'Sales'
);The inner query:
SELECT id
FROM departments
WHERE name = 'Sales';returns the department id of "Sales". The outer query uses that value to filter employees.
Types of Subqueries: Scalar, Column, Row, Table
Subqueries can return different shapes of data. You must match the shape with how you use the subquery.
Scalar Subqueries
A scalar subquery returns exactly one value (one row, one column).
You can use it anywhere that expects a single value, such as in a WHERE comparison or in the SELECT list.
Example: maximum salary in the whole company
SELECT name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);The subquery returns one value, the maximum salary. The outer query finds employees with that salary.
Example: scalar subquery in SELECT
SELECT
e.name,
e.salary,
(SELECT AVG(salary) FROM employees) AS avg_company_salary
FROM employees e;Each row shows the employee salary and the company average. The subquery runs as a scalar expression.
A scalar subquery used in a simple comparison must return at most one row.
If it returns more than one row, the query will fail with an error like "more than one row returned by a subquery used as an expression".
Column Subqueries (Single Column, Multiple Rows)
A column subquery returns one column but possibly many rows.
You usually combine it with IN, NOT IN, ANY, or ALL.
Example: employees in departments that are in a certain city
departments(id, name, city)SELECT *
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE city = 'Berlin'
);
The subquery returns a list of department ids. The outer query picks employees whose department_id is in that list.
Row Subqueries (Multiple Columns, One Row)
A row subquery returns one row with multiple columns. You can compare it with a row of columns using row syntax.
Example: find employees with maximum salary and minimum department_id at the same time
SELECT name, department_id, salary
FROM employees
WHERE (salary, department_id) = (
SELECT MAX(salary), MIN(department_id)
FROM employees
);
The subquery returns one row with two columns. The outer query compares (salary, department_id) pairwise.
Table Subqueries (Multiple Columns, Multiple Rows)
A table subquery returns many rows and columns. You use it in the FROM clause as a derived table.
Example: top earners per department, then filter
SELECT t.department_id, t.name, t.salary
FROM (
SELECT
department_id,
name,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) AS t
WHERE t.salary_rank = 1;
The subquery in FROM is a full table that the outer query selects from.
Subqueries in WHERE
The most common use of subqueries is inside WHERE.
Using `=`, `<`, `>` with Scalar Subqueries
Use a scalar subquery when you need to compare a column to a single computed value.
Example: employees who earn more than the average
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);Using `IN` with Column Subqueries
Use IN when the subquery returns a list of values.
Example: orders from VIP customers
customers(id, name, vip)
orders(id, customer_id, total_amount)SELECT *
FROM orders
WHERE customer_id IN (
SELECT id
FROM customers
WHERE vip = TRUE
);Using `EXISTS` with Correlated Subqueries
EXISTS checks if the subquery returns at least one row. It is often used with a correlated subquery, which refers to the outer query.
Example: customers who have at least one order
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);For each customer row, the subquery checks whether there is at least one order.
Rule of thumb:
Use IN for "value is in this list of values".
Use EXISTS for "there is at least one related row" and when you need a correlated condition.
Correlated vs Noncorrelated Subqueries
Noncorrelated Subqueries
A noncorrelated subquery can run on its own. It does not depend on the outer query.
Example, already seen:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);The subquery is independent. The database can run it once and reuse its result.
Correlated Subqueries
A correlated subquery refers to columns from the outer query. The database must evaluate it once per outer row.
Example: employees who earn more than the average salary in their own department
SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
The inner query depends on e.department_id from the outer query. For each department, it computes the department average salary, then compares each employee against that.
Another example using EXISTS:
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.total_amount > 1000
);
The subquery uses c.id. It is correlated.
Correlated subqueries can be much slower on large tables, because they may run once per outer row.
When performance matters, try to rewrite them using JOIN or aggregations in the outer query.
Subqueries in FROM (Derived Tables)
You can use a subquery in the FROM clause and give it an alias. The result behaves like a temporary table that exists only for the duration of the query.
General pattern:
SELECT ...
FROM (
SELECT ...
FROM some_table
WHERE ...
) AS sub
WHERE ...;Example: filter after aggregation
Find departments where the total salary expense is more than 1,000,000, and list their average salary.
SELECT d.department_id, d.total_salary, d.avg_salary
FROM (
SELECT
department_id,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS d
WHERE d.total_salary > 1000000;
The inner query calculates sums and averages per department. The outer query filters by total_salary.
Example: top N per group using a derived table
Find the top 3 highest paid employees in the company, but first calculate their ranking.
SELECT *
FROM (
SELECT
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) AS ranked
WHERE salary_rank <= 3;Without the derived table, you would not easily filter by the computed rank.
FROM (SELECT ...) AS t -- required alias
A subquery in FROM must have an alias:
Without an alias, most SQL databases will raise an error.
Subqueries in SELECT
You can also have a subquery in the SELECT list, usually scalar or correlated.
Example: company average salary next to each employee
SELECT
e.name,
e.salary,
(SELECT AVG(salary) FROM employees) AS avg_salary_company
FROM employees e;Example: count of orders per customer using a correlated subquery
SELECT
c.id,
c.name,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.id
) AS order_count
FROM customers c;This is a correlated scalar subquery. For each customer row, the subquery counts their orders.
This can usually be rewritten with a JOIN and GROUP BY, which is often more efficient:
SELECT
c.id,
c.name,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;Common Patterns With Subqueries
1. Filtering using `IN` and `NOT IN`
Customers who have placed an order
SELECT *
FROM customers
WHERE id IN (
SELECT DISTINCT customer_id
FROM orders
);Customers who have never placed an order
SELECT *
FROM customers
WHERE id NOT IN (
SELECT DISTINCT customer_id
FROM orders
);
A more robust version uses NOT EXISTS to avoid issues with NULL values:
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);2. Comparing against a subquery with `ANY` and `ALL`
ANY and ALL let you compare a value with all results of a subquery.
Employees earning more than any employee in department 5
This usually means "more than the minimum" or "more than at least one". Be careful with wording.
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 5
);
Employees earning more than all employees in department 5
This means "strictly higher than the maximum".
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 5
);3. Using subqueries to simplify complex conditions
You can use a derived table to break a problem into steps.
Example: customers with total order amount over 10,000
SELECT c.id, c.name
FROM (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS spending
JOIN customers c ON c.id = spending.customer_id
WHERE spending.total_spent > 10000;This is often easier to read than a single big query.
Typical Subquery Pitfalls
Pitfall 1: Multiple rows where one value is expected
Bad:
SELECT name
FROM employees
WHERE salary = (
SELECT salary
FROM employees
WHERE department_id = 1
);If department 1 has multiple employees, the subquery returns multiple rows, and the query fails.
Fix options:
- Use an aggregate to get a single value:
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE department_id = 1
);- Or use
INif you really want to match any of them:
WHERE salary IN (
SELECT salary
FROM employees
WHERE department_id = 1
);Pitfall 2: `NOT IN` with `NULL` values
If the subquery returns NULL, NOT IN behaves in a surprising way and can return no rows.
Bad:
SELECT *
FROM customers
WHERE id NOT IN (
SELECT customer_id
FROM orders
);
If orders.customer_id can be NULL, the result might be empty.
Safer version with NOT EXISTS:
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);Pitfall 3: Slow correlated subqueries
Correlated subqueries can look clean but be slow on large tables, because the inner query runs per row.
Example:
SELECT c.id, c.name
FROM customers c
WHERE (
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.id
) > 10;
Faster version with JOIN and GROUP BY:
SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 10;Practice Examples
Here are some practical tasks to strengthen your understanding. Try to write the queries yourself before looking at the example solutions.
Assume these tables:
employees(id, name, department_id, salary)
departments(id, name)
orders(id, customer_id, total_amount, created_at)
customers(id, name, city)Example 1: Employees in the highest paid department
Find employees who work in the department with the highest average salary.
Step 1: Find the department with highest average salary.
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;Step 2: Use as a subquery.
SELECT *
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);Example 2: Customers above average spender
Find customers whose total spending is above the average total spending of all customers.
Inner query: total spent per customer as a derived table.
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;Outer query: compare against the average of that.
SELECT c.id, c.name, s.total_spent
FROM (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS s
JOIN customers c ON c.id = s.customer_id
WHERE s.total_spent > (
SELECT AVG(total_spent)
FROM (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
) AS totals
);Notice that we have a subquery inside a subquery here. You often do not need that much nesting, but it shows how subqueries can be composed.
Example 3: Departments without employees
List departments that have no employees assigned.
Using NOT EXISTS:
SELECT d.id, d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);When to Use Subqueries vs JOINs
Often, you can solve the same problem with either subqueries or joins.
| Use case | Often better with |
|---|---|
| Simple filtering by existence | EXISTS subquery |
| Fetching related data | JOIN |
| Aggregation then filtering | Derived table subquery |
| Per-row calculated values | Correlated subquery, sometimes window functions |
| Complex logic portability | Subqueries can be clearer in steps |
Subqueries improve readability when they let you express the problem in logical steps. Joins often perform better for combining data sets.
Summary
- A subquery is a query inside another query.
- Subqueries can return:
- a single value (scalar),
- a single column list,
- a row,
- or a table.
- Common places for subqueries:
WHERE(with=,IN,EXISTS,ANY,ALL),FROM(derived tables),SELECT(computed values),HAVING(group filters).- Noncorrelated subqueries do not depend on the outer query.
- Correlated subqueries reference outer columns and run per row.
- Use
EXISTS/NOT EXISTSinstead ofIN/NOT INwhen dealing with possibleNULLvalues and existence checks. - Be careful with performance of correlated subqueries and with subqueries that might return more rows than allowed.
With these patterns and examples, you can start using subqueries to express more complex data questions in SQL.
Views: 10