Subqueries
## Learning Objectives
- Understand subquery types
- Use subqueries in WHERE
- Use subqueries in FROM
- Correlated vs uncorrelated subqueries
## What is a Subquery?
A query within a query:
```sql
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
## Subquery Types
### By Placement
- **Scalar subquery**: Returns single value
- **Column subquery**: Returns single column
- **Table subquery**: Returns entire table
### By Dependency
- **Uncorrelated**: Independent of outer query
- **Correlated**: References outer query
## Subquery in WHERE
### Comparison with Scalar
```sql
-- Employees earning above average
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
### IN Operator
```sql
-- Employees in Sales or Marketing
SELECT first_name, department
FROM employees
WHERE department IN (
SELECT department_name
FROM departments
WHERE location = 'New York'
);
```
### NOT IN (with NULL handling)
```sql
-- Employees NOT in certain departments
SELECT first_name
FROM employees
WHERE department NOT IN (
SELECT department_name
FROM departments
WHERE is_active = 1
);
```
### EXISTS / NOT EXISTS
```sql
-- Departments with employees
SELECT department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
```
### ANY / ALL
```sql
-- Salary greater than ANY of these values
WHERE salary > ANY (SELECT salary FROM interns)
-- Salary greater than ALL of these values
WHERE salary > ALL (SELECT salary FROM interns)
```
## Subquery in FROM
### Derived Table
```sql
SELECT
department,
avg_salary
FROM (
SELECT
department,
AVG(salary) as avg_salary
FROM employees
GROUP BY department
) AS dept_stats
WHERE avg_salary > 70000;
```
### Require Alias
```sql
-- Must alias derived tables
FROM (SELECT ...) AS alias_name
```
## Subquery in SELECT
### Scalar Subquery
```sql
SELECT
first_name,
salary,
(SELECT AVG(salary) FROM employees) as avg_salary,
salary - (SELECT AVG(salary) FROM employees) as diff_from_avg
FROM employees;
```
### JOIN Alternative
```sql
-- Same result using JOIN
SELECT
e.first_name,
e.salary,
dept.avg_salary
FROM employees e
JOIN (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
) dept ON e.department = dept.department;
```
## Correlated Subqueries
### What Makes It Correlated
References outer query:
```sql
SELECT
e.first_name,
e.salary,
e.department
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
```
### For Each Employee
1. Take employee
2. Calculate average for their department
3. Compare salary to that average
## Subquery Optimization
### JOIN vs Subquery
```sql
-- Subquery
SELECT *
FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE location = 'New York'
);
-- JOIN (often faster)
SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'New York';
```
### Modern Optimizers
Most databases optimize subqueries to JOINs automatically.
## Common Patterns
### Top N per Group
```sql
SELECT *
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department = e1.department
);
```
### Rows with Max in Group
```sql
SELECT *
FROM employees
WHERE (department, salary) IN (
SELECT department, MAX(salary)
FROM employees
GROUP BY department
);
```
### Find Missing Records
```sql
-- IDs not in another table
SELECT id
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees);
```
## Subquery with NULL
### Problem
```sql
-- NOT IN with NULLs returns unexpected results
WHERE id NOT IN (SELECT department_id FROM employees)
-- If any department_id is NULL, no rows match!
```
### Solution
```sql
WHERE id NOT IN (
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
);
```
## EXISTS vs COUNT(*)
```sql
-- EXISTS (stops at first match - faster)
WHERE EXISTS (SELECT 1 FROM table WHERE condition)
-- COUNT (counts all - slower)
WHERE (SELECT COUNT(*) FROM table WHERE condition) > 0
```
## Summary
- Subqueries nest inside other queries
- Scalar subqueries return single value
- IN / NOT IN for multiple values
- EXISTS / NOT EXISTS for existence checks
- Correlated subqueries reference outer query
- JOINs often perform better than subqueries
- Handle NULLs carefully with NOT IN
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →