Correlated subqueries in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a correlated subquery in SQL?

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

beginner

How does 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, because it depends on outer query values.

intermediate

Example: What does this query do?<br>

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

This query finds employees whose salary is greater than the average salary of their own department. The subquery uses e1.department from the outer query, so it is correlated.

intermediate

Why can correlated subqueries be slower than regular subqueries?

Because the correlated subquery runs once for each row of the outer query, it can be slower especially on large tables. It repeats work multiple times.

intermediate

Can a correlated subquery appear in the SELECT clause?

Yes, a correlated subquery can appear in the SELECT clause to calculate a value for each row, like a computed column based on related data.

What does a correlated subquery depend on?

A Only constants

B Values from the outer query

C Only the inner query's tables

D No dependencies

A correlated subquery depends on values from the outer query to run for each row.

Which of these is true about correlated subqueries?

A They run once per row of the outer query

B They always return multiple rows

C They cannot use outer query columns

D They run once for the entire query

Correlated subqueries run once for each row of the outer query because they depend on outer values.

In MySQL, where can a correlated subquery appear?

A Only in JOIN conditions

B Only in FROM clause

C Only in WHERE clause

D In WHERE, SELECT, or HAVING clauses

Correlated subqueries can appear in WHERE, SELECT, or HAVING clauses to filter or compute values.

What is a common use case for correlated subqueries?

A Creating indexes

B Joining two unrelated tables

C Comparing each row to an aggregate of related rows

D Updating multiple tables at once

Correlated subqueries often compare each row to aggregates like averages or counts from related rows.

Why might you avoid correlated subqueries on large tables?

A They can be slow due to repeated execution

B They use too much disk space

C They do not work with indexes

D They cause syntax errors

Correlated subqueries can be slow because they run once per outer row, repeating work many times.

Explain what a correlated subquery is and how it works in a SQL query.

Think about how the inner query uses data from the outer query.

Describe a scenario where using a correlated subquery is helpful and what to watch out for.

Consider real-life examples like comparing a student's score to class average.