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

Mindmapping tool

A digression from this blog's theme.

I discovered Freemind yesterday.

Freemind is a tool for creating mind-maps on your PC.
Mind-mapping is an alternative technique pioneered by Tony Buzan for putting thoughts on paper.

I used mind-mapping in my university days and I did not do too badly using this then unconventional technique to study.

I am excited to use it for my work now.
Finding a tool to use it on my PC is great.

Links

• Subscribe to this feed