Is there ever a case in SQL where a subquery is more efficient than a join?

Question

I've seen people hypothetically say that there are cases when a subquery can be more efficient than a join but I have never actually seen a good example of this?

What would be a case when you would want to use a subquery over a join?

Answer 1

The classic example is searching for rows in a table that do not have corresponding rows in another.

SELECT a.*
    FROM TableA a
    WHERE NOT EXISTS(SELECT NULL FROM TableB b WHERE b.parent_id = a.id)

is generally better than

SELECT a.* 
    FROM TableA a
        LEFT JOIN TableB b
            ON a.id = b.parent_id
    WHERE b.parent_id IS NULL

See also: Left outer join vs NOT EXISTS

Answer 2

When using EXISTS with a sub-query the sub-query solution should be faster (compared to an outer join and checking for NULL), because the "evaluation" of the sub-query terminates as soon as the first row is returned.

My experience is that most of the time the query optimizer chooses the same plan anyway, so that there is no performance difference between both of them (at least with Oracle and PostgreSQL)