Geek Answers Handbook

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

I have seen people hypothetically say that there are times when a subquery can be more efficient than a join, but have I never seen a good example from this?

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

+7
source share
2 answers

A classic example is finding rows in a table that don't have matching rows in another.

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

usually 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 versus NOT EXISTS

+4
source

When using EXISTS with a subquery, the subquery solution should be faster (compared to the outer join and validation for NULL), since the "evaluation" of the subquery completes as soon as the first row is returned.

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

+2
source

More articles:


All Articles