Correlated Subqueries — Row-by-Row Subqueries

---

title: “Correlated Subqueries” part: 2 topic_number: 7 slug: “correlated-subqueries” difficulty: “Intermediate” prerequisites: “nested-subqueries” —

Correlated Subqueries — Row-by-Row Subqueries

What Is It?

A correlated subquery is a subquery that references columns from the outer query. Unlike regular subqueries (which run once), correlated subqueries run once for each row processed by the outer query.

Real-world analogy: Like asking “How do I compare to my peers?” — the answer depends on who “I” am, so you have to answer it separately for each person.

When you’d use it:

• Compare each row to aggregates of related rows
• Find “top N per group”
• Row-by-row conditional logic
• Complex filtering based on related data

Warning: Can be slow on large tables (runs many times).

---

Why This Is Confusing

A regular subquery runs once and produces a fixed value. A correlated subquery runs once per row and produces a different value each time, because it uses a column from the current row.

Regular subquery:
  SELECT AVG(salary) FROM employees   →  runs once → 72000 (same for every row)

Correlated subquery:
  SELECT AVG(salary) FROM employees e2
  WHERE e2.department_id = e.department_id   →  runs per row:
    Row 1: Alice is in dept 1 → avg salary of dept 1 = 85000
    Row 2: Frank is in dept 2 → avg salary of dept 2 = 87500
    Row 3: Carol is in dept 3 → avg salary of dept 3 = 69000
    ...and so on for every employee

The e.department_id is what makes it correlated — it’s from the outer query’s current row.

---

---

Syntax Breakdown

SELECT columns
FROM table1 outer
WHERE condition (
    SELECT ...
    FROM table2 inner
    WHERE inner.col = outer.col  -- References outer query!
);

Key difference from regular subquery:

• Regular: WHERE col > (SELECT AVG(salary) FROM employees) — runs once
• Correlated: WHERE col > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) — runs per row

---

Basic Examples

Find Above-Department-Average Earners

SELECT 
    first_name,
    last_name,
    salary,
    department_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- Correlated!
)
ORDER BY department_id, salary DESC;

Expected output:

first_name	last_name	salary	department_id
Sam	Clark	95000.00	1
Paul	Garcia	87000.00	1
Frank	Miller	93000.00	2
Bob	Smith	82000.00	3
Henry	Moore	92000.00	4
…	…	…	…

Employees earning more than their department’s average.

How it works:

• For each employee, subquery calculates AVG salary in THEIR department
• Runs N times (once per employee)

EXISTS with Correlation

EXISTS demystified: WHERE EXISTS (subquery) returns TRUE if the subquery finds at least one row, FALSE if it finds none. The SELECT 1 inside is just a placeholder — you don’t care what it returns, only whether any row matched.

Think of it as: "Does a match exist? Yes/No?"
SELECT 1 FROM ... WHERE ...
   → found rows  → EXISTS = TRUE  → include this outer row
   → no rows     → EXISTS = FALSE → exclude this outer row

Find employees assigned to at least one project:

SELECT 
    first_name,
    last_name,
    employee_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employee_projects ep
    WHERE ep.employee_id = e.employee_id  -- Correlated!
)
ORDER BY last_name;

Expected output:

first_name	last_name	employee_id
Jack	Anderson	9
David	Brown	4
Sam	Clark	7
Carol	Williams	3
…	…	…

Employees with at least one project.

Why EXISTS? Faster than IN for existence checks — stops at first match.

---

Going Deeper

NOT EXISTS (Find Missing Relationships)

Find employees NOT assigned to any project:

SELECT 
    first_name,
    last_name,
    employee_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_projects ep
    WHERE ep.employee_id = e.employee_id
)
ORDER BY last_name;

Expected output:

first_name	last_name	employee_id
Leo	Jackson	15
Quinn	Martinez	17
Mia	White	18

Employees without project assignments.

Correlated Subquery in SELECT

Show each employee with their department’s highest salary:

SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    (SELECT MAX(salary)
     FROM employees e2
     WHERE e2.department_id = e.department_id
    ) AS dept_max_salary,
    CASE 
        WHEN salary = (SELECT MAX(salary)
                       FROM employees e2
                       WHERE e2.department_id = e.department_id)
        THEN 'Top Earner'
        ELSE 'Not Top'
    END AS is_top_earner
FROM employees e
ORDER BY department_id, salary DESC;

Expected output:

first_name	last_name	salary	department_id	dept_max_salary	is_top_earner
Sam	Clark	95000.00	1	95000.00	Top Earner
Paul	Garcia	87000.00	1	95000.00	Not Top
Alice	Johnson	75000.00	1	95000.00	Not Top
Frank	Miller	93000.00	2	93000.00	Top Earner
…	…	…	…	…	…

Identifies top earner in each department.

Find “Second Highest” Per Group

Find employees with the 2nd highest salary in their department:

SELECT 
    first_name,
    last_name,
    salary,
    department_id
FROM employees e
WHERE 2 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
      AND e2.salary >= e.salary
)
ORDER BY department_id;

Expected output:

first_name	last_name	salary	department_id
Paul	Garcia	87000.00	1

How it works:

• For each employee, counts how many distinct salaries in their dept are >= their salary
• If count = 2, they’re the 2nd highest

Update with Correlated Subquery

Give raises to employees earning below their department average:

UPDATE employees e
SET salary = salary * 1.05
WHERE salary < (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

What happens: Each employee below their dept average gets a 5% raise.

Check results:

SELECT first_name, salary, department_id
FROM employees
ORDER BY department_id, salary;

Pause and Predict: Why does EXISTS typically perform better than IN for large datasets?

Answer

**EXISTS stops at the first match!** - `EXISTS` returns TRUE as soon as it finds one row → stops searching - `IN` retrieves ALL matching values, then checks membership → processes everything For "does at least one exist?" questions, EXISTS is much faster on large tables.

---

Watch Out — Common Mistakes

Mistake #1: Forgetting Correlation (Makes Subquery Too Broad)

--  WRONG (not correlated properly)
SELECT first_name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees);
-- This compares to COMPANY average, not department average

Fix: Add correlation:

-- • CORRECT (department average)
SELECT first_name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id);

Mistake #2: Self-Including in Aggregate

--  LOGIC ERROR (includes self in average)
SELECT first_name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id);
-- Compares to average INCLUDING themselves

Often not an issue, but to exclude self:

-- • EXCLUDE SELF
SELECT first_name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id
                  AND e2.employee_id != e.employee_id);

Mistake #3: Performance Issues on Large Tables

--  SLOW (runs subquery N times)
SELECT 
    first_name,
    (SELECT COUNT(*) FROM employee_projects ep WHERE ep.employee_id = e.employee_id) AS project_count
FROM employees e;

For 10,000 employees, this runs 10,000 subqueries!

Better (use JOIN):

-- • FASTER
SELECT 
    e.first_name,
    COUNT(ep.project_id) AS project_count
FROM employees e
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
GROUP BY e.employee_id, e.first_name;

---

Edge Case Spotlight

Correlated DELETE

Delete employees who earn less than 80% of their department’s average:

DELETE FROM employees
WHERE employee_id IN (
    SELECT e.employee_id
    FROM employees e
    WHERE e.salary < (
        SELECT AVG(e2.salary) * 0.8
        FROM employees e2
        WHERE e2.department_id = e.department_id
    )
);

Dangerous! Always test with SELECT first:

SELECT employee_id, first_name, salary
FROM employees e
WHERE e.salary < (
    SELECT AVG(e2.salary) * 0.8
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Correlated Subquery with HAVING

Find departments where at least one employee earns more than company average:

SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.department_id = d.department_id
      AND e2.salary > (SELECT AVG(salary) FROM employees)
);

---

Try This

Exercise 1 (Guided)

Find employees who have been assigned to MORE projects than the average for their department. Show first_name, last_name, department_id, and their project_count.

Hint

Join employees with employee_projects, GROUP BY, then use HAVING with a correlated subquery that calculates average project count for the department.

Exercise 2 (Independent)

Find the highest-paid employee in each department using EXISTS. Show first_name, last_name, salary, and department_id.

Technique: An employee is the highest paid if NOT EXISTS(another employee in same dept with higher salary).

Hint

WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > e.salary)

Exercise 3 (Challenge)

Find “solo projects” — projects where only one employee is assigned. Show project_name and the employee’s first_name and last_name.

Hint

Use a correlated subquery to count employees per project: WHERE (SELECT COUNT(*) FROM employee_projects WHERE project_id = p.project_id) = 1

---

Answer Key

Exercise 1 Answer

```sql SELECT e.first_name, e.last_name, e.department_id, COUNT(ep.project_id) AS project_count FROM employees e JOIN employee_projects ep ON e.employee_id = ep.employee_id GROUP BY e.employee_id, e.first_name, e.last_name, e.department_id HAVING COUNT(ep.project_id) > ( SELECT AVG(proj_counts.cnt) FROM ( SELECT e2.department_id, COUNT(ep2.project_id) AS cnt FROM employees e2 JOIN employee_projects ep2 ON e2.employee_id = ep2.employee_id WHERE e2.department_id = e.department_id GROUP BY e2.employee_id ) AS proj_counts ) ORDER BY project_count DESC; ``` **Expected output:** | first_name | last_name | department_id | project_count | |------------|-----------|---------------|---------------| | Alice | Johnson | 1 | 4 | | Jack | Anderson | 5 | 2 | Employees with above-department-average project loads.

Exercise 2 Answer

```sql SELECT first_name, last_name, salary, department_id FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > e.salary ) ORDER BY department_id; ``` **Expected output:** | first_name | last_name | salary | department_id | |------------|-----------|---------|---------------| | Sam | Clark | 95000.00 | 1 | | Frank | Miller | 93000.00 | 2 | | Bob | Smith | 82000.00 | 3 | | Henry | Moore | 92000.00 | 4 | | ... | ... | ... | ... | Top earner from each department. **How it works:** For each employee, checks if anyone else in their dept earns more. If not, they're the highest paid.

Exercise 3 Answer

```sql SELECT p.project_name, e.first_name, e.last_name FROM projects p JOIN employee_projects ep ON p.project_id = ep.project_id JOIN employees e ON ep.employee_id = e.employee_id WHERE ( SELECT COUNT(*) FROM employee_projects ep2 WHERE ep2.project_id = p.project_id ) = 1 ORDER BY p.project_name; ``` **Expected output:** | project_name | first_name | last_name | |--------------|------------|-----------| | Marketing Campaign | Quinn | Rodriguez | Projects with only one assigned employee.

---

Quick Recap

• Correlated subqueries reference outer query columns
• Run once per row (unlike regular subqueries)
• Use for row-by-row comparisons to aggregates
• EXISTS/NOT EXISTS excellent for “does relationship exist?” questions
• Often slower than JOINs — use when logic demands it
• Can appear in SELECT, WHERE, HAVING
• Useful for “top N per group”, comparisons within groups
• Always test with SELECT before using in UPDATE/DELETE

---

Part 2 Complete!

Congratulations! You’ve mastered intermediate SQL techniques:

• SUBSTRING and CASE WHEN for data transformation
• Self joins for hierarchical data
• Multiple joins across complex schemas
• COALESCE for NULL handling
• Nested and correlated subqueries

You’re ready for advanced topics!

---

Up Next

Time for a Challenge! → Mini Challenge 9

You’ve completed Part 2! Test your mastery of subqueries before moving to Part 3.