Joins vs Scalar-sub queries

Have you ever considered the difference between the following?

select t1.col1, t2.col2
from table1 t1 inner join
table2 t2 on t1.col3 = t2.col3

and

select t1.col1,
 (select t2.col2 from table2 t2 where t2.col3 = t1.col3)
from table1 t1

The first one is a join, whereas the second one uses something called as scalar-sub queries.

The above two will give very similar output, but the second one will be generally much slower than the first one. At least in most cases. This is because the in-line query will be run once for each row of output from the main query, and that generally proves to be much more expensive than a simple join (which accesses both the table only once).

Is there any time when the scalar sub-query works faster? Yes, sometimes distribution of data in the tables and the way indexes are designed can make the second one faster, but this rarely happens; and especially if you need multiple columns from the same table for the same condition, then scalar sub queries can be horrific .

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax