Which is efficient? query with subquery or joined table

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.

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.

There is simply no answer to this question. Even if your table structure doesn't change, queries can get different execution paths over time depending on the amount of data, the indexes, the constraints, because of bind variable peeking, and scads of other factors. Entire books have been written on the subject.

cagcowboy's answer is incorrect. Oracle will rewrite your query to provide what it thinks is the best execution plan. Queries like the one you describe are often transformed by subquery unnesting. My guess is that 9 times out of 10, queries similar to the ones you describe will have the same execution plan.

In my opinion, start with what is most readable, what will make it clearest to someone else reading your code (or yourself, reading it six months from now), what your intent is. If your query runs unacceptably slow, only then try to optimize it.

As Branko Dimitrijevic points out, two queries you think are the same often aren't equivalent. In your two examples, if salesman_name is not unique, your first query will throw a ORA-01427: single-row subquery returns more than one row exception, but your second example will work fine.