Subqueries By rows returned IN · ANY · ALL · EXISTS

Multiple-Row Subquery

When the inner query returns many rows, the outer query needs set operators: IN, ANY, ALL, EXISTS. Each has its own meaning, and NOT IN has a NULL trap worth knowing about.

What a multiple-row subquery is

A multiple-row subquery returns more than one row to the outer query — for instance, a list of department IDs, or every employee's manager_id. Single-row operators (=, >, etc.) can't handle that, so SQL provides a different family of operators built for sets:

Operator	Meaning
IN	Match any value in the set
NOT IN	Match none of the values
ANY / SOME	True if condition holds for at least one value
ALL	True only if condition holds for every value
EXISTS	True if the subquery returns at least one row

📌 Where you can put one: multiple-row subqueries appear in WHERE, HAVING, or FROM clauses. They cannot sit in the SELECT list — that position requires exactly one value per outer row.

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — IN with a list

Find employees who manage at least one other person. The inner query returns the set of manager_ids; the outer query keeps employees whose employee_id is in that set.

SQL

SELECT employee_id, first_name, last_name
FROM   employees
WHERE  employee_id IN (
    SELECT DISTINCT manager_id
    FROM   employees
    WHERE  manager_id IS NOT NULL
);

Result

employee_id	first_name	last_name
100	Steven	King
101	Neena	Kochhar
201	Michael	Hartstein

Example 2 — NOT IN

Conversely, find people who are not managers. Note the explicit IS NOT NULL — without it, a single NULL in the manager_id list would make every NOT IN comparison evaluate to UNKNOWN.

SQL

SELECT first_name, last_name
FROM   employees
WHERE  employee_id NOT IN (
    SELECT manager_id
    FROM   employees
    WHERE  manager_id IS NOT NULL
);

⚠ The NOT IN + NULL gotcha. If the subquery returns any NULL, NOT IN returns no rows. Always filter NULLs out of the inner result, or use NOT EXISTS instead — it handles NULLs correctly.

Example 3 — > ANY

> ANY (subquery) is true if the outer value is greater than at least one of the values returned. Effectively: "greater than the minimum."

SQL

-- Employees who earn more than at least one employee in department 3
SELECT first_name, salary
FROM   employees
WHERE  salary > ANY (
    SELECT salary FROM employees WHERE department_id = 3
);

Example 4 — > ALL

> ALL (subquery) is true only when the outer value is greater than every value returned. Effectively: "greater than the maximum."

SQL

-- Employees who earn more than every employee in department 3
SELECT first_name, salary
FROM   employees
WHERE  salary > ALL (
    SELECT salary FROM employees WHERE department_id = 3
);

Combination	Equivalent to
> ANY (subquery)	> MIN(subquery)
< ANY (subquery)	< MAX(subquery)
> ALL (subquery)	> MAX(subquery)
< ALL (subquery)	< MIN(subquery)
= ANY (subquery)	IN (subquery)
<> ALL (subquery)	NOT IN (subquery)

Example 5 — EXISTS

EXISTS returns true the moment the subquery yields any row, false otherwise. The actual values returned don't matter — only their presence.

SQL

-- Departments that currently have at least one employee
SELECT d.department_id, d.department_name
FROM   departments d
WHERE  EXISTS (
    SELECT 1
    FROM   employees e
    WHERE  e.department_id = d.department_id
);

💡 Why SELECT 1? Inside EXISTS, the column list is irrelevant — only existence matters. Writing SELECT 1 (or SELECT *) is a convention; the database doesn't actually compute anything.

Recap

Multiple-row subqueries return many rows — use IN, ANY, ALL, or EXISTS.
= ANY is the same as IN; <> ALL is the same as NOT IN.
> ANY = "greater than the minimum"; > ALL = "greater than the maximum."
Watch out for NOT IN with NULLs — it silently returns nothing. Prefer NOT EXISTS when the subquery may contain NULLs.
Cannot appear in the SELECT list — only in WHERE / HAVING / FROM.