Scalar Subquery to avoid outer join

An outer join is used when there are some rows in 1 table (say t) that are not matched in another table (say t_c1) but you still want to return rows from table t.
An outer join prevents the optimizer from using the most optimal execution path and thus impacts performance negatively.

With scalar subquery (highlighted in bold below), we can avoid outer joins like this:

select t.some_column ,
   (select t_c1.some_column,
       t_c1.some_other_column
       from t_c1
       on some_column = t_c1.some_column)
from t

The above returns rows even when t.some_column does not match t_c1.some_column achieving an optional join just like outer join.

Read more about Scalar subquery and outer join here.

Links

• Subscribe to this feed