Correlated subqueries execution model in PostgreSQL

Introduction

Correlated subqueries let you compare each row of one table with rows in another table. They help answer questions where you need to check related data row by row.

When you want to find rows in one table that depend on values in another table for each row.

When filtering data based on a condition that involves matching or comparing with another table's data.

When calculating values that depend on each row's related data, like checking if a customer has orders above a certain amount.

When you need to write queries that can't be done easily with simple joins or aggregates.

When you want to check existence or conditions for each row individually using another table.

Syntax

PostgreSQL

SELECT column1, column2
FROM table1
WHERE columnX operator (
    SELECT columnY
    FROM table2
    WHERE table2.columnZ = table1.columnA
);

The subquery refers to a column from the outer query, making it 'correlated'.

The subquery runs once for each row of the outer query, which can affect performance.

Examples

This finds employees whose salary is above the average salary of their own department.

PostgreSQL

SELECT e.name
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

This finds customers who have at least one order with amount greater than 100.

PostgreSQL

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.amount > 100
);

Sample Program

This query lists employees who earn more than the average salary in their department. The subquery calculates the average salary for the department of each employee row.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 4000),
('Carol', 'HR', 4500),
('Dave', 'HR', 5500);

SELECT name, salary, department
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

Important Notes

Correlated subqueries can be slower because the inner query runs for each outer row.

Sometimes you can rewrite correlated subqueries using JOINs for better performance.

Understanding how the subquery depends on the outer query helps avoid mistakes.

Summary

Correlated subqueries run the inner query once per outer row, using values from that row.

They are useful for row-by-row comparisons or checks involving related tables.

Be mindful of performance and consider alternatives like JOINs when possible.

Practice

1. What is a key characteristic of a correlated subquery in PostgreSQL?

easy

A. It runs the inner query once for each row of the outer query.

B. It runs the inner query only once regardless of outer rows.

C. It always returns a fixed number of rows.

D. It cannot reference columns from the outer query.

Solution

1. Step 1: Understand the definition of correlated subquery

   A correlated subquery references columns from the outer query and runs once per outer row.

2. Step 2: Compare options with this behavior

   Only It runs the inner query once for each row of the outer query. correctly states that the inner query runs once per outer row.

3. Final Answer:

   It runs the inner query once for each row of the outer query. -> Option A

4. Quick Check:

   Correlated subquery = runs per outer row [OK]

Hint: Correlated means inner query depends on outer row [OK]

Common Mistakes:

• Thinking inner query runs once for all rows
• Assuming no reference to outer query columns
• Confusing correlated with uncorrelated subqueries

2. Which of the following is the correct syntax for a correlated subquery in PostgreSQL?

easy

A. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department);

B. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees);

C. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.salary > 50000);

D. SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.name = 'John');

Solution

1. Step 1: Identify the correlated subquery condition

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); references the outer query alias e.department inside the inner query, making it correlated.

2. Step 2: Check other options for correlation

   Options B, C, and D do not reference outer query columns inside the subquery, so they are not correlated.

3. Final Answer:

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department); -> Option A

4. Quick Check:

   Correlation requires outer reference inside subquery [OK]

Hint: Look for outer query column inside subquery WHERE clause [OK]

Common Mistakes:

• Missing outer query reference in subquery
• Using same alias for outer and inner queries
• Confusing filtering with correlation

3. Given the tables departments(id, name) and employees(id, name, department_id, salary), what will this query return?

SELECT d.name, (SELECT AVG(e.salary) FROM employees e WHERE e.department_id = d.id) AS avg_salary FROM departments d;

medium

A. Each department name with the overall average salary of all employees.

B. Each department name with the average salary of its employees.

C. An error because subquery is not correlated.

D. Each employee name with their salary.

Solution

1. Step 1: Identify the correlation in the subquery

   The subquery uses e.department_id = d.id, linking inner query to outer query row.

2. Step 2: Understand the output

   For each department, the subquery calculates average salary of employees in that department.

3. Final Answer:

   Each department name with the average salary of its employees. -> Option B

4. Quick Check:

   Correlated subquery returns per outer row result [OK]

Hint: Check if subquery filters by outer query column [OK]

Common Mistakes:

• Assuming subquery returns overall average salary
• Thinking subquery is uncorrelated and runs once
• Confusing department and employee columns

4. Identify the error in this correlated subquery:

SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM employees e1 WHERE e1.department_id = e1.department_id);

medium

A. The query is missing a GROUP BY clause.

B. The subquery is missing a WHERE clause.

C. The subquery references a non-existent column.

D. The inner query alias shadows the outer query alias causing incorrect correlation.

Solution

1. Step 1: Analyze alias usage in inner and outer queries

   Both inner and outer queries use alias e1, causing the inner query to reference itself only.

2. Step 2: Understand impact on correlation

   Because of alias shadowing, the subquery condition e1.department_id = e1.department_id is always true and not correlated to outer row.

3. Final Answer:

   The inner query alias shadows the outer query alias causing incorrect correlation. -> Option D

4. Quick Check:

   Use different aliases to avoid shadowing [OK]

Hint: Use distinct aliases for outer and inner queries [OK]

Common Mistakes:

• Using same alias for inner and outer queries
• Assuming WHERE clause is missing
• Confusing alias shadowing with syntax error

5. You want to find departments where the highest employee salary is greater than the average salary of all employees in that department. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees));

B. SELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id;

C. SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id);

D. SELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Understand the requirement

   We need to compare the max salary per department with the average salary per department.

2. Step 2: Analyze each option's logic

   SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); uses two correlated subqueries referencing d.id to get max and average salary per department, correctly implementing the condition.

3. Step 3: Identify errors in other options

   SELECT d.name FROM departments d WHERE MAX(e.salary) > AVG(e.salary) FROM employees e WHERE e.department_id = d.id; has invalid syntax mixing aggregation and WHERE clause. SELECT d.name FROM departments d JOIN employees e ON e.department_id = d.id WHERE e.salary > (SELECT AVG(salary) FROM employees); compares employee salary to overall average, not per department. SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > (SELECT AVG(salary) FROM employees)); compares employee salary to overall average, not per department.

4. Final Answer:

   SELECT d.name FROM departments d WHERE (SELECT MAX(e.salary) FROM employees e WHERE e.department_id = d.id) > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = d.id); -> Option C

5. Quick Check:

   Use correlated subqueries for per-department aggregates [OK]

Hint: Use correlated subqueries for per-group comparisons [OK]

Common Mistakes:

• Mixing aggregation without GROUP BY
• Comparing to overall average instead of per department
• Incorrect syntax for aggregation in WHERE clause