When to use sub-query and when to use join [message #486201] |
Tue, 14 December 2010 04:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/132904.jpg) |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hi all,
In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?
This is I think subjective, I want all those different thoughts.
regards,
Delna
|
|
|
|
|
|
|
|
|
Re: When to use sub-query and when to use join [message #486264 is a reply to message #486221] |
Tue, 14 December 2010 09:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Once upon a time I attended a SQL statement tuning class at Oracle's Costa Mesa facility. The course and material were intended for Oracle 8.1.5. The instructor stressed that sub-queries (especially correlated sub-queries) were inherently slow and strongly recommended using joins instead whenever possible. The execution plans for sub-queries, whether in IN clauses or correlated sub-queries showed nested loops and did not use indexes. That was a few versions ago and the optimizer has gotten better. I still tend to prefer join conditions and find such queries easier to read. The following section of the current documentation describes how the optimizer unnests the sub-query to form a join condition to get the better execution plan. I figure why not just use the join condition to begin with?
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF10104
[Updated on: Tue, 14 December 2010 09:54] Report message to a moderator
|
|
|
|
Re: When to use sub-query and when to use join [message #486346 is a reply to message #486335] |
Wed, 15 December 2010 01:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:because Oracle has to do some extra work for that transformation.
No, Oracle does the work anyway and then compare the cost of both to know which one is cheaper (roughly describing).
Regards
Michel
|
|
|