Which is efficient? query with subquery or joined table

Question

Here are two example of query for the same purpose (in this example, I want to fine salesman from same city where Patriks lives.)

select * 
from salesman 
where salesman.city = 
     ( select city 
       from salesman 
       where salesman_name='Patriks'
     );

and

select s1.* 
from salesman s1,salesman s2 
where s1.city=s2.city 
  and s2.salesman_name='Patriks';

Which is better or efficient and why? (I know this is small example, but I want to figure out, which will be good in complex situation, and for big database.)

Answer 1

As a general rule of thumb:

If you use a subquery, you force Oracle to use a certain execution path (ie it must execute the sub-query before it can execute the outer query)

If you use a join, Oracle is free to pick whichever it considers to be the most efficient path.

I would always go for the join over the subquery therefore. YMMV.

Answer 2

My experience is that in Oracle, a flattened query (that is, the one with the join) is often more efficient than an equivalent query using a subselect. It seems that in the more complex cases, there are query paths that the Oracle optimiser doesn't find, for a query with a subselect.

In SQL Server, DB2, Ingres and Sybase, my experience is that it makes no difference - these DBMSs have optimisers that will find the same query paths, regardless of whether you use a flattened query or a query with a subselect.

I don't have sufficient experience of other DBMSs to comment on these.

But that's just my experience. I wouldn't be too surprised if you find different results for particular queries, or particular sets of data. The best thing to do is to try out both and see which query works better, for your situation.