Why to use Correlated Subqueries?

As far as I’ve seen, correlated subqueries can be re-written using multiple-column subqueries or joins. And they usually perform better than correlated subqueries.

So in which possible scenarios a correlated subquery can be a better option or the only option? (I use Oracle Database)

Answer

Oracle has a good optimizer, but correlated subqueries are sometimes the most efficient way to express a query. For instance:

select t.*,
       (select count(*) from z where z.t_id = t.id)
from t;

can be quite efficient with an index on z(t_id) because it avoids the outer aggregation.

There are other cases where they are both efficient and directly translate into a question: Fetch all the ts that don’t exist in z.

select t.*
from t
where not exists (select 1 from z where z.id = t.id);

And finally, correlated subqueries are just an example of lateral joins. And lateral joins can be quite powerful. For instance, to get all the columns from the previous row, you might use:

select t.*, t2.*
from t cross join lateral
     (select t2.*
      from t t2
      where t2.date < t.date
      order by t2.date desc
      fetch first 1 row only
     ) t2;