Correlated subqueries execution model in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a correlated subquery in SQL?

A correlated subquery is a subquery that refers to a column from the outer query. It runs once for each row processed by the outer query.

intermediate

How does PostgreSQL execute a correlated subquery?

PostgreSQL executes the correlated subquery once for each row of the outer query, using the current row's values to evaluate the subquery.

intermediate

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

Because the correlated subquery runs repeatedly for each row of the outer query, it can cause many executions and slow down the query.

beginner

What is an example of a correlated subquery condition?

Example: SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e1.department); Here, the subquery uses e1.department from the outer query.

advanced

How can you optimize queries with correlated subqueries in PostgreSQL?

You can rewrite correlated subqueries as JOINs or use WITH clauses (CTEs) to reduce repeated executions and improve performance.

What does a correlated subquery depend on?

A Columns from the outer query

B Only constants

C Only aggregate functions

D No external references

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

How often is a correlated subquery executed in PostgreSQL?

A Once for the entire query

B Only once per table

C Once per row of the outer query

D Never executed

It runs once for each row processed by the outer query.

Which SQL clause often contains a correlated subquery?

A WHERE

B FROM

C GROUP BY

D ORDER BY

Correlated subqueries are commonly found in the WHERE clause to filter rows.

What is a common way to improve performance of correlated subqueries?

A Use indexes on unrelated columns

B Add more correlated subqueries

C Use SELECT * always

D Rewrite as JOINs or CTEs

Rewriting correlated subqueries as JOINs or CTEs can reduce repeated executions.

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

A It is a constant value

B It links the subquery to the outer query

C It is ignored

D It is an aggregate function

e1.department links the subquery to the current row of the outer query, making it correlated.

Explain how PostgreSQL executes a correlated subquery and why it might affect performance.

Think about how many times the subquery runs.

Describe a strategy to optimize a query that uses a correlated subquery in PostgreSQL.

Consider alternative SQL structures.