Correlated subquery execution model in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a correlated subquery in SQL?

A correlated subquery is a subquery that depends on values from the outer query. It runs once for each row processed by the outer query.

intermediate

How does the execution model of a correlated subquery differ from a regular subquery?

A regular subquery runs once and returns a result set. A correlated subquery runs repeatedly, once for each row of the outer query, using values from that row.

intermediate

Why can correlated subqueries be slower than non-correlated subqueries?

Because correlated subqueries execute once per outer row, they may run many times, causing slower performance compared to subqueries that run only once.

beginner

Give an example of a correlated subquery in SQL.

Example:
SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department);
This subquery depends on e1.department from the outer query.

beginner

What is the role of the outer query in a correlated subquery execution?

The outer query provides values to the subquery for each row, controlling how many times the subquery runs and what data it uses.

How often does a correlated subquery execute in relation to the outer query?

A Once for each row of the outer query

B Only once before the outer query

C Only once after the outer query

D It does not execute

A correlated subquery runs once for each row processed by the outer query because it depends on values from that row.

Which of the following best describes a correlated subquery?

A A subquery that returns multiple columns

B A subquery that runs independently

C A subquery that uses values from the outer query

D A subquery that is always faster

A correlated subquery depends on the outer query's current row values to execute.

Why might correlated subqueries cause slower query performance?

A Because they run multiple times, once per outer row

B Because they do not use indexes

C Because they return too many columns

D Because they run only once

Correlated subqueries execute repeatedly for each outer row, increasing total execution time.

In the query: SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department); what is e1.department?

A A constant value

B A value from the outer query used in the subquery

C A column from the subquery table only

D An alias for the subquery

e1.department is from the outer query and is used inside the subquery, making it correlated.

Which statement is true about correlated subqueries?

A They always return a single value

B They execute only once and do not depend on the outer query

C They cannot be used in WHERE clauses

D They depend on the outer query and execute repeatedly

Correlated subqueries depend on the outer query and execute once per outer row.

Explain how a correlated subquery executes in relation to its outer query.

Think about how the subquery needs data from each row of the outer query.

Describe a real-life example where a correlated subquery might be useful.

Imagine checking if a person's salary is above their department's average.

Practice

1. What is a correlated subquery in SQL?

easy

A. A subquery that runs independently of the outer query

B. A subquery that uses values from the outer query to filter results

C. A query that joins two tables without conditions

D. A query that only returns aggregate values

Solution

1. Step 1: Understand subquery types

   A correlated subquery depends on the outer query's current row to run.

2. Step 2: Identify correlation

   It uses columns from the outer query inside the subquery's WHERE clause.

3. Final Answer:

   A subquery that uses values from the outer query to filter results -> Option B

4. Quick Check:

   Correlated subquery = uses outer query values [OK]

Hint: Correlated subqueries reference outer query columns [OK]

Common Mistakes:

• Thinking subquery runs once independently
• Confusing with JOIN operations
• Assuming it returns only aggregates

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

easy

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

B. SELECT e.name FROM employees e WHERE salary > 50000

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

D. SELECT e.name FROM employees e JOIN departments d ON e.department = d.id

Solution

1. Step 1: Identify correlation in subquery

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department) uses 'e.department' inside the subquery, linking it to the outer query.

2. Step 2: Check other options

   SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees) has no reference to outer query in subquery; the option with 'salary > 50000' lacks a subquery and the JOIN option is not a subquery.

3. Final Answer:

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

4. Quick Check:

   Correlation needs outer query column inside subquery [OK]

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

Common Mistakes:

• Missing outer query reference inside subquery
• Confusing JOIN with subquery
• Using subquery without correlation

3. Given the tables employees(id, name, department, salary) and the query:

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

What does this query return?

medium

A. Employees whose salary is above the average salary of their own department

B. Employees whose salary is above the average salary of all employees

C. Employees with salary above 50000

D. All employees regardless of salary

Solution

1. Step 1: Understand the subquery correlation

   The subquery calculates average salary for the department of the current employee (e1.department).

2. Step 2: Compare salaries

   The outer query selects employees whose salary is greater than that department average.

3. Final Answer:

   Employees whose salary is above the average salary of their own department -> Option A

4. Quick Check:

   Salary > department average = Employees whose salary is above the average salary of their own department [OK]

Hint: Check which outer column is used inside subquery condition [OK]

Common Mistakes:

• Assuming average is for all employees
• Ignoring the correlation condition
• Confusing with simple WHERE salary > value

4. Identify the error in the following correlated subquery:

SELECT c.customer_id FROM customers c WHERE c.orders_count > (SELECT AVG(o.orders_count) FROM orders o WHERE o.customer_id = c.customer_id);

medium

A. The subquery will return multiple rows causing an error

B. The subquery uses a wrong table alias 'o' which is not defined

C. The subquery compares orders_count incorrectly; should use SUM instead of AVG

D. The subquery references the outer query correctly; no error

Solution

1. Step 1: Analyze correlation

   The subquery correctly uses 'c.customer_id' from the outer query in its WHERE clause.

2. Step 2: Check aggregation and output

   AVG(o.orders_count) is an aggregate that returns a single scalar value, even for customers with multiple orders.

3. Final Answer:

   The subquery references the outer query correctly; no error -> Option D

4. Quick Check:

   Subquery must return single value for comparison [OK]

Hint: Ensure subquery returns one value for comparison [OK]

Common Mistakes:

• Thinking AVG returns multiple rows without GROUP BY
• Assuming alias 'o' is undefined
• Believing SUM is needed instead of AVG

5. You want to find all products whose price is higher than the average price of products in the same category. Which query correctly uses a correlated subquery to achieve this?

hard

A. SELECT p.product_name FROM products p JOIN categories c ON p.category = c.id WHERE p.price > c.avg_price

B. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products)

C. SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category)

D. SELECT product_name FROM products WHERE price > ALL (SELECT price FROM products)

Solution

1. Step 1: Identify correlation condition

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) uses 'p.category' inside the subquery to calculate average price per category, correlating outer and inner queries.

2. Step 2: Verify other options

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products) compares to overall average, not per category; the option using JOIN with categories uses a JOIN but no subquery; the option using > ALL compares to all individual prices, not average.

3. Final Answer:

   SELECT p.product_name FROM products p WHERE p.price > (SELECT AVG(price) FROM products WHERE category = p.category) -> Option C

4. Quick Check:

   Correlated subquery filters by category [OK]

Hint: Use outer query column inside subquery WHERE for correlation [OK]

Common Mistakes:

• Using overall average instead of per category
• Confusing JOIN with subquery
• Using ALL instead of AVG in subquery