# Expressing multiple repeated joins as a correlated subquery


In [yesterday’s post](https://datawise.dev/revisiting-group-by-rollup-with-a-more-realistic-example), we looked at retrieving information from a table by joining it multiple times—each with different join criteria. This raises a natural question: are there better alternatives to this approach?

I initially experimented with a CASE WHEN in the join condition, hoping it would short-circuit, picking the first matching condition—just like in a SELECT clause. However, in a join, it evaluates all scenarios, so that didn’t work as expected.

But remember correlated subqueries? A correlated subquery runs once per row and can be embedded in the SELECT or WHERE clause. Essentially, it lets you create a dynamic query within a single data cell, based on the current row’s context. Check out [this quick intro](https://datawise.dev/using-correlated-subqueries-in-bigquery).

To avoid multiple joins, you can use a correlated subquery to fetch all possible combinations (previously handled by join conditions) and apply the same logic with ORDER BY and LIMIT to return exactly one value.

A word of caution: correlated subqueries execute once per row, which can impact performance, especially with large datasets. However, they’re a valuable tool in your SQL tool belt, particularly when other elegant solutions aren’t available.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1740323268891/8607984d-3445-4040-bfab-d5b29cd62f6a.jpeg align="center")

*Found it useful? Subscribe to my Analytics newsletter at* [***notjustsql.com***](https://notjustsql.com/)*.*

---

*Enjoyed this? Here are some related articles you might find useful:*

- [Self-joins in SQL](https://datawise.dev/self-joins-in-sql)
- [Anti-joins in SQL](https://datawise.dev/anti-joins-in-sql)
- [SEMI-JOINS in SQL](https://datawise.dev/semi-joins-in-sql)
- [NON-EQUI joins in SQL](https://datawise.dev/non-equi-joins-in-sql)