Correlated subqueries execution model in PostgreSQL - Time & Space Complexity

Time Complexity: Correlated subqueries execution model

O(n * m)

Understanding Time Complexity

When using correlated subqueries, the database runs a small query repeatedly for each row of the main query.

We want to understand how the total work grows as the main table gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

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

This query finds employees whose salary is above the average salary in their department using a correlated subquery.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: For each employee row, the subquery calculates the average salary for that employee's department.
• How many times: The subquery runs once per employee in the main query.

How Execution Grows With Input

As the number of employees grows, the subquery runs more times, once per employee.

Input Size (n)	Approx. Operations
10	About 10 subquery executions
100	About 100 subquery executions
1000	About 1000 subquery executions

Pattern observation: The total work grows roughly in direct proportion to the number of employees.

Final Time Complexity

Time Complexity: O(n * m)

This means the total work grows linearly with the number of rows in the main table times the average number of employees per department.

Common Mistake

[X] Wrong: "The subquery runs only once, so the query is very fast regardless of table size."

[OK] Correct: Because the subquery depends on each row of the main query, it runs repeatedly, increasing total work as the table grows.

Interview Connect

Understanding how correlated subqueries scale helps you write efficient queries and explain performance in real projects.

Self-Check

"What if we replaced the correlated subquery with a join and group by? How would the time complexity change?"

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