Sub Queries vs. Outer Joins : (SELECT of a SELECT) vs. (LEFT OUTER JOINS)

One performance bottleneck that as Java developers we may encounter may be our poor use of SQL.

I often see a Nested SELECT like:

SELECT name FROM bbc
WHERE population >
(SELECT population FROM bbc
WHERE name='Russia')


Implemented as a LEFT OUTER JOIN
SELECT name FROM BBC LEFT OUTER JOIN ON BBC

Dependant on the query, the Nested SELECT technique may force the subquery to be evaluated for every row in the left-hand table.
A LEFT OUTER join, by contrast, can often use a much more efficient query plan.

This is not always the case as they are mathematically equivalent and a good query optimizer may generate the same query plan, but this is not always the case.