Correlated Subqueries Simplified
A correlated subquery is a subquery that depends on the outer query for its execution. Unlike normal subqueries that run once, a correlated subquery runs again and again — once for each row processed by the outer query. Because of this dependency, correlated subqueries cannot run independently and can affect performance if not handled properly.
In a correlated subquery, the inner query depends on values from the outer query. This relationship allows the query to evaluate data row by row, making correlated subqueries especially useful for comparing records within the same table or across related tables.
Syntax:
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator ( SELECT column
FROM table2 inner
WHERE inner.column1 = outer.column1);Let's look at some simple examples of a correlated subquery using the DVD rental database.
Example1: Find films whose rental rate is higher than the average rental rate of films with similar rating.
SELECT title, rental_rate, rating
FROM film f
WHERE rental_rate (SELECT AVG(rental_rate)
FROM film
WHERE rating = f.rating);The inner query uses f.rating from the outer query, which means it references each film and retrieves its rating. For every film from the outer query, the subquery calculates the average rental rate for that rating category in the subquery. The outer query then displays all films whose rental rate is greater than the average value calculated by the inner query. Since the inner query depends on the outer query, the entire query is evaluated row by row for all films.
Output:
The subquery uses a value from the outer query. This creates a dependency, which makes it a correlated subquery.
Let’s look at one more example of a correlated subquery using the EXISTS operator.
Example2: Show the details of films that have at least one actor
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE EXISTS (SELECT 1 FROM payment p
WHERE p.customer_id = c.customer_id
)
ORDER BY customer_id;This query uses EXISTS operator: The SQL EXISTS operator is a boolean operator used within a WHERE (or HAVING) clause to check for the existence of any rows returned by a subquery.
It returns TRUE, if the subquery produces at least one row
It returns FALSE, if the subquery returns no rows.
In the above query, the inner query takes customer_id from the outer query and checks if it exists in the payment table. If the customer has made a payment, their customer_id will be present in the payment table, the EXISTS operator returns TRUE, and the customer is included in the final result. If the customer has not made any payments, their customer_id won’t be found, the EXISTS operator returns FALSE, and the customer is excluded from the output.
Output:
Inside EXISTS, we often write SELECT 1, because the actual column values are not needed only existence of rows matters. It is a best practice to use 1 with SELECT inside EXISTS, as it improves readability.
Correlated subqueries are powerful when you need row-by-row validation or filtering based on related data, or comparing data with same table. When used with EXISTS, they provide a clean and readable way to check relationships between tables.
We can also use the NOT EXISTS operator with correlated subqueries to handle negative scenarios.
Let's look at the example below using NOT EXISTS operator.
Example3: Show the details of films that have never been rented
SELECT * FROM film f
WHERE NOT EXISTS (
SELECT 1 FROM inventory i
JOIN rental r
ON i.inventory_id = r.inventory_id
WHERE i.film_id = f.film_id
);NOT EXISTS :
EXISTS returns TRUE if the subquery finds at least one row.
NOT EXISTS returns TRUE if the subquery finds no rows.
So for each film:
If no inventory record of that film appears in the rental table, the film is returned to the output.
If at least one rental exists, the film is excluded in the final output.
The subquery looks at the inventory table to find copies of a film. Joins with the rental table to see if those copies were ever rented. Uses i.film_id = f.film_id to correlate/reference the subquery with the current film from the outer query.
Output:
Performance Considerations:
However, correlated subqueries can slow down queries on large tables since the subquery runs for every row in the outer table.
Optimization techniques:
To improve performance:
Avoid unnecessary subqueries by using joins where possible.
Index the columns used in correlated subqueries appropriately.
Combine correlated subqueries with advanced SQL features like CTEs and window functions to enhance query power and flexibility.
For large datasets, using joins or CTEs is often a faster and cleaner alternative.
Conclusion:
Correlated subqueries are a powerful tool for row-by-row comparisons and checking relationships between tables. While they can impact performance on large datasets, understanding their use with EXISTS and NOT EXISTS, along with optimization techniques like joins, CTEs, and indexing, can make your SQL queries both effective and efficient.
For a complete guide on subqueries, don’t miss my detailed blog — click the link below to learn more!
Happy Querying!
