Introduction to Subqueries

A **subquery** is a SQL query written inside another SQL statement.

The query inside is called the **inner query** or **subquery**. The query that uses its result is called the **outer query**.

Imagine an employees table containing employee names and salaries. You want to find employees earning more than the company's average salary.

You could calculate the average manually and enter it into another query. However, the result would need updating whenever salaries changed.

A subquery lets SQL calculate the average dynamically.

```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 whose salaries are greater than that result.

### How to understand the query

1. The inner query calculates AVG(salary).
2. Its result is used by the outer query's WHERE condition.
3. The outer query returns employees who satisfy that condition.

### Where can subqueries appear?

- **SELECT:** Display a calculated value alongside each result row.
- **WHERE:** Filter rows using a query result.
- **FROM:** Create a derived table.
- **HAVING:** Filter groups using a subquery result.
- **Data-modification statements:** Supply or filter values for INSERT, UPDATE, and DELETE operations.

This lesson focuses on SELECT, WHERE, and FROM, then introduces correlated subqueries and existence checks.

We will use a simple employees table throughout this lesson.

If you already have an employees table, use a separate practice database or choose another table name to avoid conflicts.

### Step 1: 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
);
```

### Step 2: Insert sample data

```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);
```

### Step 3: View the data

```sql
SELECT *
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 |

We will use these six rows to understand how subqueries work.

A **scalar subquery** returns one column and at most one row. This allows its result to be used where SQL expects a single value.

### Example 1: Calculate the average salary

```sql
SELECT AVG(salary) AS average_salary
FROM employees;
```

Expected output:

| average_salary |
|---:|
| 60000.00 |

The total salary is 360000. Dividing by six employees gives an average of 60000.

### Example 2: Find employees earning above 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 |

Asha earns exactly 60000, so she does not qualify for the strict greater-than comparison.

### Scalar subquery rules

- It must return exactly one column.
- It must return no more than one row.
- If it returns zero rows, its scalar result is NULL.
- If it returns multiple rows, PostgreSQL raises an error when a scalar value is required.

An aggregate such as AVG without GROUP BY normally returns one row, even if there are no input rows. In that case, AVG returns NULL.

Use scalar subqueries when you need one calculated value, such as an average, maximum, minimum, or lookup result.

A subquery can supply a value for comparison operators such as >, <, >=, <=, and =.

### Example 1: Find the highest salary

```sql
SELECT MAX(salary) AS highest_salary
FROM employees;
```

Expected result: 80000.00.

### Example 2: Find employees earning less than the highest salary

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

Expected output:

| employee_name | salary |
|---|---:|
| Arjun | 70000.00 |
| Asha | 60000.00 |
| Neha | 55000.00 |
| Kabir | 50000.00 |
| Meera | 45000.00 |

Rahul is excluded because his salary equals the maximum.

### Example 3: Find the minimum-paid employee

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

Expected output:

| employee_name | salary |
|---|---:|
| Meera | 45000.00 |

If multiple employees share the minimum salary, all of them are returned.

The inner query does not need to return an employee name. It returns a salary, and the outer query finds the employees whose salaries match it.

**Remember:** Use a subquery when a comparison value should be calculated from the current database rather than hardcoded.

Some subqueries return multiple rows rather than a single value.

Suppose you want employees who work in a department containing at least one employee earning more than 75000.

### Step 1: Find the qualifying departments

```sql
SELECT DISTINCT department
FROM employees
WHERE salary > 75000;
```

Expected output:

| department |
|---|
| Engineering |

### Step 2: Use the result in an outer query

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

Expected output:

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

The inner query identifies Engineering. The outer query returns every employee whose department appears in that result.

Conceptually, the condition is similar to:

```sql
WHERE department IN ('Engineering')
```

The subquery keeps the department list dynamic.

### Important details

- The subquery used by IN must return one column.
- It can return multiple rows.
- Duplicate values in the inner result do not multiply outer rows.
- If the subquery returns no rows, IN matches no rows.
- NULL values need special care, particularly with NOT IN.

Use IN when you need to check membership in a set of values.

A subquery inside FROM produces a result set that the outer query treats like a table. This is called a **derived table**.

### Example: Calculate department averages

```sql
SELECT department, average_salary
FROM (
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
) AS department_stats
WHERE average_salary > 55000
ORDER BY average_salary DESC;
```

Expected output:

| department | average_salary |
|---|---:|
| Engineering | 70000.00 |
| Sales | 62500.00 |

The inner query calculates these department averages:

| department | average_salary |
|---|---:|
| Engineering | 70000.00 |
| HR | 47500.00 |
| Sales | 62500.00 |

The outer query treats this result as a table named department_stats and keeps only departments with an average greater than 55000.

### Why is the alias required?

In PostgreSQL, a subquery used as a FROM item must have an alias.

Here, the alias is:

```sql
AS department_stats
```

You can reference its columns using department_stats.department and department_stats.average_salary.

### When are derived tables useful?

They help divide complicated queries into logical stages:

1. Calculate a summary.
2. Filter the summary.
3. Sort the final results.

A derived table exists only within the query. It does not permanently create a database table.

A scalar subquery can appear in the SELECT list to display a calculated value alongside each result row.

### Example: Display every salary and the company average

```sql
SELECT
employee_name,
salary,
(
SELECT AVG(salary)
FROM employees
) AS company_average
FROM employees
ORDER BY employee_id;
```

Expected output:

| employee_name | salary | company_average |
|---|---:|---:|
| Asha | 60000.00 | 60000.00 |
| Rahul | 80000.00 | 60000.00 |
| Meera | 45000.00 | 60000.00 |
| Arjun | 70000.00 | 60000.00 |
| Neha | 55000.00 | 60000.00 |
| Kabir | 50000.00 | 60000.00 |

The scalar subquery supplies the company average, which is displayed alongside each employee.

### An alternative using a window function

```sql
SELECT
employee_name,
salary,
AVG(salary) OVER () AS company_average
FROM employees
ORDER BY employee_id;
```

Both queries can produce the same values for this dataset.

They are different SQL techniques. Window functions are especially useful when you want calculations across rows without collapsing the result into one row per group.

Do not assume one form is always faster. Choose the clearest correct expression and investigate execution plans when performance matters.

A **non-correlated subquery** does not refer to a column from the outer query.

Example:

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

The inner query calculates the overall average without referring to an individual outer employee.

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

### Example: Find employees earning above their department average

```sql
SELECT
e.employee_name,
e.department,
e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department = e.department
)
ORDER BY e.department, e.salary DESC;
```

Expected output:

| employee_name | department | salary |
|---|---|---:|
| Rahul | Engineering | 80000.00 |
| Arjun | Sales | 70000.00 |

Look at the condition:

```sql
WHERE e2.department = e.department
```

- e2 refers to the employees rows inside the subquery.
- e refers to the employee row from the outer query.
- The subquery calculates the average for the department of the current outer employee.

The outer employee is returned only if their salary is greater than their department's average.

### A common misunderstanding

A correlated subquery is logically dependent on outer-query values. That does not mean PostgreSQL must physically execute it from scratch for every outer row. The optimizer may choose different execution strategies.

Correlated subqueries can be useful and expressive, but investigate complex queries with EXPLAIN when performance matters.

EXISTS checks whether a subquery returns at least one row. NOT EXISTS checks whether it returns no rows.

These operators are useful when you want to test whether related records exist without displaying the inner query's columns.

### Create a departments table

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

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

### Find departments that have 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 that department in the sample data.

### 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 |

### Why SELECT 1?

EXISTS only needs to determine whether a row exists. The selected value is not used to construct the outer result.

SELECT 1 is a common convention, but SELECT * would also work for EXISTS.

Use EXISTS for questions such as whether a customer has an order or a department has an employee. Use NOT EXISTS to find customers without orders, departments without employees, or products without sales.

NULL requires special care when using subqueries.

Suppose a subquery returns these values:

```text
Engineering
HR
NULL
```

Now consider a comparison such as:

```sql
WHERE department NOT IN (
SELECT department_name
FROM some_table
)
```

For a department that does not match Engineering or HR, the NULL in the list can cause the comparison to evaluate to UNKNOWN rather than TRUE.

A WHERE clause retains only rows for which the condition evaluates to TRUE. Consequently, the query may return fewer rows than expected.

### A safer anti-join pattern

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

With the sample tables, this returns no rows because every employee's department exists in departments.

### Important distinction

NOT EXISTS avoids the specific problem of a NULL in the inner result making NOT IN comparisons UNKNOWN.

However, equality inside NOT EXISTS does not match NULL to NULL. If your matching columns can be NULL and your business rule treats two NULLs as equal, you must handle that case explicitly.

Always consider NULL behavior when designing exclusion queries.

Subqueries and JOINs can sometimes express similar requirements, but they are not interchangeable in every situation.

### Using IN

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

### Using a JOIN

```sql
SELECT DISTINCT
e.employee_name,
e.department
FROM employees AS e
JOIN employees AS high_earner
ON high_earner.department = e.department
WHERE high_earner.salary > 75000
ORDER BY e.employee_name;
```

Both queries can return employees in departments containing someone who earns more than 75000.

The JOIN uses DISTINCT because multiple matching high earners could otherwise produce duplicate employee rows.

The IN query checks membership and does not multiply an outer row simply because the inner result contains the same department multiple times.

### Which should you use?

- Use a scalar subquery for a single calculated value.
- Use IN to check membership in a set.
- Use EXISTS to check whether related rows exist.
- Use a JOIN when you need columns from multiple tables or want to combine related records.
- Use a derived table when a staged result makes a query easier to understand.

Neither subqueries nor JOINs are automatically faster in every situation. PostgreSQL may transform or execute them in different ways. Use EXPLAIN and representative data when performance matters.

A subquery is a way of expressing a SQL operation, not a guarantee about how PostgreSQL physically executes it.

PostgreSQL may optimize a subquery into a join, use a semi-join or anti-join strategy, or choose another plan appropriate to the query.

### Inspect the planned operations

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

EXPLAIN displays the planned operations without executing the SELECT as EXPLAIN ANALYZE does.

### 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. Be careful when applying it to statements that modify data, because those statements actually execute.

### Practical guidelines

1. Select only the columns you need.
2. Check that filtering and matching conditions express the intended relationship.
3. Consider indexes that fit the query and workload, but do not assume an index will always be used.
4. Compare alternative queries using representative data.
5. Do not assume a correlated subquery must physically execute once per outer row.
6. Preserve duplicate and NULL behavior when rewriting a query.

For the small practice table in this lesson, performance differences may be insignificant. Correctness and understanding the results come first.

Use the employees and departments tables created earlier.

### Exercise 1: Above-average salary

Return employees whose salary is greater than the overall average salary.

### Exercise 2: Below-maximum salary

Return employee names and salaries for everyone earning less than the maximum salary.

### Exercise 3: Department membership

Use IN to find employees who work in a department containing at least one employee earning more than 75000.

### Exercise 4: Department averages

Use a derived table in FROM to calculate each department's average salary. Return only departments with an average greater than 55000.

### Exercise 5: Above-department-average employees

Use a correlated subquery to find employees who earn more than their own department's average salary.

### Exercise 6: Empty departments

Use NOT EXISTS to find departments that do not have any employees.

### Exercise 7: Scalar-subquery behavior

What happens if a scalar subquery returns two rows instead of one?

### Exercise 8: NULL and NOT IN

Why can NOT IN behave unexpectedly when its subquery returns NULL?

### Exercise 9: Existence checks

You need to return records only when at least one related record exists. Which SQL operator is designed for this?

### Exercise 10: Explain the query

What does this query return?

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

### Solution 1: Above-average salary

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

Expected employees: Rahul and Arjun.

### Solution 2: Below-maximum salary

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

Expected employees: Arjun, Asha, Neha, Kabir, and Meera.

### Solution 3: Department membership

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

Expected employees: Asha and Rahul.

### Solution 4: Department averages

```sql
SELECT department, average_salary
FROM (
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
) AS department_stats
WHERE average_salary > 55000
ORDER BY average_salary DESC;
```

Expected departments: Engineering and Sales.

### Solution 5: Above-department-average employees

```sql
SELECT
e.employee_name,
e.department,
e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department = e.department
)
ORDER BY e.department, e.salary DESC;
```

Expected employees: Rahul and Arjun.

### Solution 6: Empty departments

```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 result: Marketing.

### Solution 7: Scalar-subquery behavior

PostgreSQL raises an error when a scalar subquery returns multiple rows where one value is required.

### Solution 8: NULL and NOT IN

NULL can cause comparisons to evaluate to UNKNOWN. WHERE keeps only TRUE conditions, so rows that you expected to retain may be excluded.

### Solution 9: Existence checks

Use EXISTS.

### Solution 10: Explain the query

The inner query calculates the overall average salary. The outer query returns employees whose salaries are strictly greater than that average.

Choose the best answer for each question.

### 1. What is a subquery?

A query nested inside another SQL statement. Its result is used by the outer query.

### 2. What is an inner query?

The nested query that supplies a value or result set to the outer query.

### 3. What is a scalar subquery?

A subquery returning one column and at most one row, suitable for use as a single value.

### 4. What happens when a scalar subquery returns no rows?

Its scalar result is NULL.

### 5. What happens when a scalar subquery returns multiple rows?

PostgreSQL raises an error when a scalar value is required.

### 6. Can a subquery return multiple rows?

Yes. For example, a subquery used with IN can return several values.

### 7. What is a subquery in WHERE?

A nested query used to determine whether an outer row satisfies a condition.

### 8. What is a derived table?

A subquery used in the FROM clause as a table-like source.

### 9. Why does a derived table need an alias in PostgreSQL?

The alias names the result source so the outer query can reference its columns.

### 10. What is a correlated subquery?

A subquery that refers to a column from the outer query, such as the current employee's department.

### 11. What is a non-correlated subquery?

A subquery that does not refer to columns from the outer query.

### 12. What is the difference between IN and EXISTS?

IN checks whether a value belongs to a set of values. EXISTS checks whether a subquery returns at least one row.

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

It checks that no row satisfies the subquery's conditions and is useful for finding records without related rows.

### 14. Why can NOT IN be problematic with NULL?

A NULL in the subquery result can cause nonmatching comparisons to evaluate to UNKNOWN rather than TRUE.

### 15. Can subqueries be used in SELECT?

Yes. A scalar subquery can calculate a value displayed alongside each outer row.

### 16. Can subqueries be used in HAVING?

Yes. A subquery can supply a value used to filter groups.

### 17. Are subqueries and JOINs interchangeable?

Not always. They can express similar requirements, but duplicate behavior and semantics must be considered.

### 18. Are correlated subqueries always executed once for every outer row?

No. That is not a guaranteed physical execution strategy. PostgreSQL may choose a different plan.

### 19. How can you inspect a subquery's execution plan?

Use EXPLAIN to inspect planned operations or EXPLAIN ANALYZE to execute the query and report actual execution information.

### 20. When should you use a subquery?

Use one when it clearly expresses the relationship between a calculated result and the outer query, such as comparing salaries to an average, checking membership, or testing whether related records exist.

## Summary

- A subquery is a query nested inside another SQL statement.
- The inner query supplies a result used by the outer query.
- Scalar subqueries return one column and at most one row.
- A scalar subquery returning zero rows produces NULL; one returning multiple rows causes an error when a scalar value is required.
- WHERE subqueries can compare values against dynamic results.
- IN checks membership in a set of values.
- A subquery in FROM creates a derived table and requires an alias in PostgreSQL.
- A SELECT-list subquery can display a calculated value alongside each output row.
- Correlated subqueries refer to values from the outer query.
- EXISTS and NOT EXISTS test whether matching rows exist.
- NULL values require special care with NOT IN.
- Subqueries are not automatically slower than JOINs.
- EXPLAIN and EXPLAIN ANALYZE help investigate query plans and execution behavior.

## Before moving on

- [ ] Explain inner queries and outer queries.
- [ ] Write a scalar subquery that calculates an average.
- [ ] Filter employees using a calculated maximum or average.
- [ ] Use IN with a multi-row subquery.
- [ ] Write a derived table in FROM with an alias.
- [ ] Explain correlated and non-correlated subqueries.
- [ ] Use EXISTS to test for related rows.
- [ ] Use NOT EXISTS to find rows without a match.
- [ ] Explain the NULL issue with NOT IN.
- [ ] Recognize when a JOIN may be clearer than a subquery.

**Next lesson:** Continue with set operations to combine the results of multiple queries.

Avoid these common errors when writing subqueries.