Subqueries

What Is a Subquery?

A subquery is a SQL query placed inside another query. Subqueries are also called nested queries or inner queries.

Subqueries in WHERE

SELECT name, salary
FROM EMPLOYEE
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);

The query above finds employees whose salary is above the average.

Subqueries with IN

SELECT name
FROM EMPLOYEE
WHERE id department IN (
    SELECT department_id
    FROM DEPARTMENT
    WHERE location = 'Chicago'
);

Subqueries in FROM (Derived Tables)

SELECT d.department_name, sub.amount
FROM DEPARTMENT d
JOIN (
    SELECT department_id, COUNT(*) AS amount
    FROM EMPLOYEE
    GROUP BY department_id
) sub ON d.department_id = sub.department_id;

Subqueries in SELECT

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM EMPLOYEE) AS average
FROM EMPLOYEE;

EXISTS - Checking for Data Existence

SELECT name
FROM EMPLOYEE k
WHERE EXISTS (
    SELECT 1 FROM TRANSACTION t
    WHERE t.employee_id = k.employee_id
);

Correlated Subqueries

A correlated subquery runs the inner query for every row of the outer query.

SELECT name, salary, department
FROM EMPLOYEE k1
WHERE salary = (
    SELECT MAX(salary)
    FROM EMPLOYEE k2
    WHERE k2.department = k1.department
);

Subquery Tips

• Use subqueries only when other solutions are more complicated
• Make sure the subquery returns the appropriate number of columns
• Watch performance on large datasets

Subqueries are an important technique. Next, we will learn UNION, INTERSECT & EXCEPT.