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: <br>SELECT e1.name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department); <br>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.