Single-Row and Multiple-Row Subqueries

A **subquery** is a SQL query written inside another SQL statement. The outer query uses the result returned by the inner query.

Subqueries can return different kinds of results.

### Single-row subquery

A single-row subquery returns at most one row and one column when used as a scalar value.

Example:

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
```

The inner query calculates the average salary. The outer query returns employees earning more than that average.

### Multiple-row subquery

A multiple-row subquery returns several values that the outer query can compare against a set.

Example:

```sql
SELECT employee_name, department
FROM employees
WHERE department IN (
SELECT department
FROM employees
WHERE salary > 75000
);
```

The inner query identifies departments with an employee earning more than 75000. The outer query returns employees belonging to those departments.

### The main difference

- A scalar comparison such as salary = (subquery) expects one scalar value.
- IN, ANY, and ALL can compare against a subquery that returns multiple rows.
- EXISTS checks whether the subquery returns any rows.

A query's result shape must match how the outer query uses it.

The following PostgreSQL examples use a small employees table.

If you already created this table in Lesson 33, reuse it instead of running the setup again.

### Create the table

```sql
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
salary NUMERIC(10, 2) NOT NULL
);
```

### Insert sample records

```sql
INSERT INTO employees (
employee_id,
employee_name,
department,
salary
)
VALUES
(1, 'Asha', 'Engineering', 60000.00),
(2, 'Rahul', 'Engineering', 80000.00),
(3, 'Meera', 'HR', 45000.00),
(4, 'Arjun', 'Sales', 70000.00),
(5, 'Neha', 'Sales', 55000.00),
(6, 'Kabir', 'HR', 50000.00);
```

### View the records

```sql
SELECT employee_id, employee_name, department, salary
FROM employees
ORDER BY employee_id;
```

Expected output:

| employee_id | employee_name | department | salary |
|---:|---|---|---:|
| 1 | Asha | Engineering | 60000.00 |
| 2 | Rahul | Engineering | 80000.00 |
| 3 | Meera | HR | 45000.00 |
| 4 | Arjun | Sales | 70000.00 |
| 5 | Neha | Sales | 55000.00 |
| 6 | Kabir | HR | 50000.00 |

All salary examples below use these six records.

Single-row subqueries are useful when the outer query needs to compare a column with one calculated value.

Common operators include:

- `=` equal to
- `<>` or `!=` not equal to
- `>` greater than
- `<` less than
- `>=` greater than or equal to
- `<=` less than or equal to

### Example 1: Find employees earning more than the average

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary DESC;
```

Expected output:

| employee_name | salary |
|---|---:|
| Rahul | 80000.00 |
| Arjun | 70000.00 |

The average salary is 60000. Only salaries strictly greater than 60000 qualify.

### Example 2: Find the highest-paid employee

```sql
SELECT employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
```

Expected output:

| employee_name | salary |
|---|---:|
| Rahul | 80000.00 |

If several employees share the highest salary, the query returns all of them.

### Example 3: Find employees earning less than the maximum

```sql
SELECT employee_name, salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
ORDER BY salary DESC;
```

This returns every employee except Rahul in the sample dataset.

### What if the subquery returns multiple rows?

Consider:

```sql
SELECT employee_name
FROM employees
WHERE salary = (
SELECT salary
FROM employees
WHERE department = 'Engineering'
);
```

The inner query returns two salaries: 60000 and 80000. PostgreSQL raises an error because the equality comparison expects one scalar value.

If multiple values are intended, use IN, ANY, or ALL, depending on the requirement.

IN checks whether a value matches one of the values returned by a subquery.

### Example: Employees in departments with a high earner

```sql
SELECT employee_name, department
FROM employees
WHERE department IN (
SELECT department
FROM employees
WHERE salary > 75000
)
ORDER BY employee_name;
```

The inner query returns Engineering because Rahul earns 80000.

Expected output:

| employee_name | department |
|---|---|
| Asha | Engineering |
| Rahul | Engineering |

Although the inner query identifies a department using one employee, the outer query returns all employees in that department.

### How IN works

This condition:

```sql
department IN ('Engineering', 'Sales')
```

is true when department matches either value.

The subquery version calculates those values dynamically.

### Important rules

- The subquery must return one column for this use of IN.
- It may return zero, one, or many rows.
- Duplicate values in the subquery do not multiply the outer query's rows.
- If the subquery is empty, IN evaluates to FALSE for a non-NULL left-hand value.
- If NULL appears in the result, a nonmatching comparison can evaluate to UNKNOWN.

Use IN when you want to ask, "Does this value belong to the set returned by the inner query?"

ANY compares a value against the values returned by a subquery. The condition is true if at least one individual comparison is true.

In PostgreSQL, SOME is a synonym for ANY.

### Example: Salaries greater than ANY Sales salary

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
ORDER BY salary;
```

The Sales salaries are 70000 and 55000.

The condition means:

```text
salary > 70000
OR
salary > 55000
```

It is sufficient for either comparison to be true. For this particular set, the condition is equivalent to salary > 55000.

Expected output:

| employee_name | salary |
|---|---:|
| Asha | 60000.00 |
| Rahul | 80000.00 |

Neha earns 55000, which is not greater than either Sales salary.

### ANY with equality

```sql
SELECT employee_name
FROM employees
WHERE salary = ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
);
```

This behaves like IN for ordinary equality comparisons against the returned set.

### ANY and NULL

If no comparison is TRUE but at least one comparison is UNKNOWN because of NULL, the overall result can be UNKNOWN.

If the subquery returns no rows, ANY evaluates to FALSE.

**Remember:** ANY means at least one comparison must be true. It does not mean every comparison must be true.

ALL compares a value against every value returned by a subquery. The condition is true only when all individual comparisons are true.

### Example: Salaries greater than ALL Sales salaries

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
ORDER BY salary DESC;
```

Sales salaries are 70000 and 55000.

The condition means:

```text
salary > 70000
AND
salary > 55000
```

Both comparisons must be true. Here, that is equivalent to salary > 70000.

Expected output:

| employee_name | salary |
|---|---:|
| Rahul | 80000.00 |

Arjun earns 70000, so he does not qualify because the comparison is strictly greater than 70000.

### Example: Salaries less than ALL Engineering salaries

```sql
SELECT employee_name, salary
FROM employees
WHERE salary < ALL (
SELECT salary
FROM employees
WHERE department = 'Engineering'
)
ORDER BY salary;
```

Engineering salaries are 60000 and 80000. A salary must be below both values, which is equivalent to being below 60000.

Expected output:

| employee_name | salary |
|---|---:|
| Meera | 45000.00 |
| Kabir | 50000.00 |
| Neha | 55000.00 |

### ALL and empty results

If the subquery returns no rows, ALL evaluates to TRUE. This follows the logical rule that every comparison in an empty set satisfies the condition.

This behavior can surprise developers, so consider whether an empty subquery should qualify records under your business rules.

### Quick comparison

| Operator | Meaning |
|---|---|
| `> ANY (...)` | Greater than at least one value |
| `> ALL (...)` | Greater than every value |
| `= ANY (...)` | Equivalent to IN for ordinary equality comparisons |
| `< ALL (...)` | Less than every value |

**Memory trick:** ANY means at least one; ALL means every one.

EXISTS checks whether a subquery returns at least one row.

The values selected inside the subquery are not used to construct the outer result. Only the existence of a matching row matters.

### Create a departments table

```sql
CREATE TABLE departments (
department_name VARCHAR(50) PRIMARY KEY
);

INSERT INTO departments (department_name)
VALUES
('Engineering'),
('HR'),
('Sales'),
('Marketing');
```

If this table already exists from Lesson 33, do not recreate it.

### Find departments with employees

```sql
SELECT d.department_name
FROM departments AS d
WHERE EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department = d.department_name
)
ORDER BY d.department_name;
```

Expected output:

| department_name |
|---|
| Engineering |
| HR |
| Sales |

Marketing is excluded because no employee belongs to it.

### Why SELECT 1?

EXISTS only tests whether a row is returned. The value 1 is a conventional placeholder; SELECT * would also work.

### Why use EXISTS instead of a regular JOIN?

A JOIN can produce multiple outer-result rows when several inner rows match. EXISTS tests for a match without multiplying an outer row merely because multiple matching rows exist.

The database optimizer may choose an efficient execution strategy for EXISTS. It is not a guarantee that the database must stop at a particular physical operation or use a specific index.

A correlated subquery refers to a value from the outer query.

In the previous example, this condition connects the inner and outer queries:

```sql
WHERE e.department = d.department_name
```

The department name comes from the current outer row.

### Find employees who belong to an existing department

```sql
SELECT e.employee_name, e.department
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM departments AS d
WHERE d.department_name = e.department
)
ORDER BY e.employee_name;
```

All six sample employees qualify because each employee's department exists in the departments table.

### Find departments without employees

```sql
SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department = d.department_name
)
ORDER BY d.department_name;
```

Expected output:

| department_name |
|---|
| Marketing |

NOT EXISTS is useful for finding records without related records.

### Real-world examples

- Customers who have placed at least one order.
- Products that have never been purchased.
- Departments without employees.
- Users who have not completed a required task.
- Orders that have no matching payment record.

### Important NULL distinction

NOT EXISTS avoids the specific problem where a NULL in a NOT IN subquery causes nonmatching comparisons to become UNKNOWN.

However, ordinary equality does not match NULL to NULL. If nullable keys must be treated as equal, define that rule explicitly, for example by using PostgreSQL's IS NOT DISTINCT FROM where appropriate.

Subquery conditions follow SQL's three-valued logic: TRUE, FALSE, and UNKNOWN.

### Example: A set containing NULL

Suppose a subquery returns:

```text
60000
80000
NULL
```

Consider:

```sql
salary NOT IN (
SELECT salary
FROM some_table
)
```

For a salary that matches neither 60000 nor 80000, the comparison with NULL can make the overall NOT IN result UNKNOWN.

WHERE retains only rows for which the condition is TRUE. This is why NOT IN can exclude rows you expected to retain.

### Safer anti-join pattern

```sql
SELECT e.employee_name
FROM employees AS e
WHERE NOT EXISTS (
SELECT 1
FROM some_table AS s
WHERE s.salary = e.salary
);
```

This avoids the NULL-in-the-inner-result issue. It does not automatically treat NULL outer and inner values as equal.

### Empty result sets

For an empty subquery result:

- x IN (empty set) is FALSE.
- x = ANY (empty set) is FALSE.
- x > ALL (empty set) is TRUE.
- EXISTS (empty result) is FALSE.
- NOT EXISTS (empty result) is TRUE.

These results follow SQL's set-comparison and existence rules.

### Business-rule check

Before writing an exclusion query, ask:

1. Can the subquery return NULL?
2. Can the outer comparison value be NULL?
3. Should NULL be considered a match?
4. What should happen when the inner query returns no rows?

These questions prevent subtle filtering bugs.

Use the operator that matches the requirement.

### Requirement: Compare against one calculated value

Use a scalar subquery.

```sql
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
```

### Requirement: Match one of several values

Use IN.

```sql
WHERE department IN (
SELECT department
FROM employees
WHERE salary > 75000
)
```

### Requirement: Greater than at least one returned value

Use ANY.

```sql
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
```

### Requirement: Greater than every returned value

Use ALL.

```sql
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
```

### Requirement: At least one matching record exists

Use EXISTS.

```sql
WHERE EXISTS (
SELECT 1
FROM departments AS d
WHERE d.department_name = e.department
)
```

### Requirement: No matching record exists

Use NOT EXISTS.

```sql
WHERE NOT EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department = d.department_name
)
```

A correct query should reflect the intended relationship and handle NULL values appropriately. Do not choose an operator based only on the number of lines of SQL it requires.

Correctness comes first. Once the query is correct, you can examine its performance.

### Inspect the planned operations

```sql
EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
```

EXPLAIN shows the planned operations without executing this SELECT in the same way as EXPLAIN ANALYZE.

### Inspect actual execution

```sql
EXPLAIN ANALYZE
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
```

EXPLAIN ANALYZE executes the query and reports actual execution information alongside the plan.

### Practical guidelines

- An index may help with filtering or matching, but its use is not guaranteed.
- EXISTS and JOIN can have similar or different execution plans depending on the query.
- ANY and ALL do not guarantee a particular physical execution strategy.
- Correlated syntax does not prove the database will repeatedly execute the inner query from scratch for every outer row.
- Test with realistic data volumes and distributions.
- Preserve duplicate behavior and NULL semantics when rewriting queries.

For a small six-row practice table, performance is not a meaningful reason to prefer one form. Focus on understanding the results first.

Use the employees and departments tables from this lesson.

### Exercise 1: Single-row subquery

Return employees whose salaries are greater than the average salary of all employees.

### Exercise 2: Maximum salary

Return all employees who earn the maximum salary.

### Exercise 3: IN

Return employees who belong to a department with at least one employee earning more than 75000.

### Exercise 4: ANY

Return employees whose salaries are greater than at least one salary in Sales.

### Exercise 5: ALL

Return employees whose salaries are greater than every salary in Sales.

### Exercise 6: Correlated EXISTS

Return employees whose department exists in the departments table.

### Exercise 7: NOT EXISTS

Return departments without employees.

### Exercise 8: Scalar-subquery error

Explain why using = with a subquery that returns two salary values can fail.

### Exercise 9: NULL behavior

Explain why NOT IN may exclude rows when the inner query contains NULL.

### Exercise 10: Empty set

What does salary > ALL (an empty subquery) evaluate to?

Try writing each query before checking the solutions.

### Solution 1: Above average

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY salary DESC;
```

Expected: Rahul and Arjun.

### Solution 2: Maximum salary

```sql
SELECT employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
```

Expected: Rahul.

### Solution 3: IN

```sql
SELECT employee_name, department
FROM employees
WHERE department IN (
SELECT department
FROM employees
WHERE salary > 75000
)
ORDER BY employee_name;
```

Expected: Asha and Rahul.

### Solution 4: ANY

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
ORDER BY salary;
```

Expected: Asha and Rahul.

### Solution 5: ALL

```sql
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Sales'
)
ORDER BY salary DESC;
```

Expected: Rahul.

### Solution 6: Correlated EXISTS

```sql
SELECT e.employee_name, e.department
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM departments AS d
WHERE d.department_name = e.department
)
ORDER BY e.employee_name;
```

Expected: All six employees.

### Solution 7: NOT EXISTS

```sql
SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department = d.department_name
)
ORDER BY d.department_name;
```

Expected: Marketing.

### Solution 8: Scalar-subquery error

The equality comparison expects one value, but the subquery returns multiple rows. PostgreSQL raises an error. Use IN or another suitable operator if multiple values are intended.

### Solution 9: NULL behavior

Comparisons involving NULL can evaluate to UNKNOWN. Since WHERE retains only TRUE, the query can exclude rows that appear to be nonmatching.

### Solution 10: Empty set

The condition evaluates to TRUE because ALL over an empty set is true under SQL's set-comparison rules.

Choose one answer for each question.

### 1. What is a single-row subquery?

A subquery returning one column and at most one row when used as a scalar value.

### 2. What is a multiple-row subquery?

A subquery that returns multiple rows, commonly consumed by operators such as IN, ANY, or ALL.

### 3. Can a scalar subquery return zero rows?

Yes. Its scalar result is NULL.

### 4. What happens if a scalar subquery returns multiple rows?

PostgreSQL raises an error when one scalar value is required.

### 5. What is the difference between IN and ANY?

IN tests membership. ANY applies a comparison operator to each returned value and is true if at least one comparison is true. With equality, = ANY is equivalent to IN under SQL's ordinary comparison semantics.

### 6. What does ALL do?

ALL evaluates the comparison against every returned value. The condition must be true for every comparison.

### 7. What is the difference between > ANY and > ALL?

Greater than ANY requires the value to exceed at least one returned value. Greater than ALL requires it to exceed every returned value.

### 8. What happens when ANY receives an empty result?

ANY evaluates to FALSE.

### 9. What happens when ALL receives an empty result?

ALL evaluates to TRUE.

### 10. What is EXISTS?

EXISTS returns a Boolean result indicating whether the subquery returns at least one row.

### 11. Why use SELECT 1 with EXISTS?

The selected expression is not used to build the outer result. SELECT 1 is a conventional way to express that only row existence matters.

### 12. What is a correlated subquery?

A subquery that references a column from the outer query.

### 13. What is NOT EXISTS used for?

It finds outer rows for which the subquery returns no matching rows.

### 14. Why is NOT IN risky with NULL?

A NULL in the returned set can make nonmatching comparisons UNKNOWN, causing WHERE to exclude them.

### 15. Can EXISTS produce duplicate outer rows?

EXISTS itself does not multiply an outer row because several inner rows match. It returns a Boolean test for each outer row.

### 16. Is = ANY equivalent to IN?

For ordinary equality comparisons, yes. Both test whether a value matches at least one value in the set, including the relevant NULL behavior.

### 17. Does a correlated subquery always run once per outer row?

No. The syntax expresses a dependency, but the optimizer may choose different physical execution strategies.

### 18. How do you investigate subquery performance?

Use EXPLAIN for the planned operations and EXPLAIN ANALYZE for actual execution information. Compare queries with representative data.

### 19. When should you use ALL instead of MAX?

ALL directly expresses comparison against every returned value. MAX may express an equivalent requirement for certain non-NULL numeric cases, but empty-set and NULL behavior can differ. Choose based on the intended semantics.

### 20. When should you use EXISTS instead of IN?

EXISTS is natural when the requirement is to check for related rows. IN is natural when checking membership in a returned set. Correctness and NULL behavior should guide the choice.

## Summary

- A scalar subquery returns one column and at most one row.
- Single-row subqueries work with comparison operators such as =, <, and >.
- A scalar subquery returning multiple rows causes an error when one value is required.
- IN checks membership in a set.
- ANY requires at least one comparison to be true.
- ALL requires every comparison to be true.
- EXISTS checks whether at least one row is returned.
- NOT EXISTS finds outer rows with no matching inner rows.
- Correlated subqueries can refer to outer-query columns.
- NULL values can affect IN, NOT IN, ANY, and ALL.
- ANY over an empty result is FALSE; ALL over an empty result is TRUE.
- Query plans, rather than syntax alone, should guide performance investigations.

## Before moving to the next lesson

- [ ] Write a scalar subquery using AVG.
- [ ] Explain why a scalar subquery cannot return multiple rows.
- [ ] Use IN with a subquery.
- [ ] Explain the difference between ANY and ALL.
- [ ] Use EXISTS for a related-record check.
- [ ] Use NOT EXISTS to find unmatched records.
- [ ] Explain the NULL issue with NOT IN.
- [ ] Explain the behavior of ANY and ALL with empty results.
- [ ] Use EXPLAIN to inspect a query plan.

**Next:** Continue to the next lesson in Subqueries and Set Operations.

Avoid these frequent mistakes when using single-row and multiple-row subqueries.