aspose file tools*
The moose likes JDBC and the fly likes SQL Nested select vs Outer Join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Nested select vs Outer Join" Watch "SQL Nested select vs Outer Join" New topic
Author

SQL Nested select vs Outer Join

Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 117
Hi all,

Lets say I have two tables:


STORE
Storename || Sales || Date
Los Angeles || 1800 || 01-05-2010
San Diego || 800 || 01-06-2010
Boston || 1200 || 01-07-2010


REGION
Regionname || Storename
East || Boston
East || New York
West || Los Angeles
West || San Diego


SQL used is:

Select A1.storename as store, (select sales from store where storename = A1.storename) as sales from REGION A1

This is to list all stores per region whether there is a corresponding record on the store table or not.

This will output

storename || sales
Boston || 1200
New York || null
Los Angeles || 1800
San Diego || 800


What if I used this query instead?

Select A1.storename, A2.sales from REGION A1, STORE A2 where
A1.storename = A2.storename(+)

I know the result will be the same but I'm curious to know the effect on performance and your opinions on it. This is just a simple example by the way. The query I am working on has about 6 nested selects I am trying to optimize it by changing it to outer joins. I have done performance testing on this and so far the results are the same but my superior kept insisting to change the query to outer join instead of inner select. I argued there would be no need to change the query since the effect on performance would be the same. Would it really be better to use outer join instead? or does it have other benefits?


Thanks, I would appreciate any form of help.



Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

This generally depends on the database you're using. Some databases employ optimization which chooses the optimal access path in both cases, some database choose predetermined access plan for these two types of queries. In the latter case the join might be better if the REGION table contained lots of records.

What database(s) are you using?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Nested select vs Outer Join
 
Similar Threads
Census: Los Angeles Area Is Fastest Growing in Nation
Help with SQL
New York City: World's Greatest City
Idiocy of the American system
JOIN syntax for HQL . Need help!