File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Nested select vs Outer Join" Watch "SQL Nested select vs Outer Join" New topic

SQL Nested select vs Outer Join

Andres Delrotti
Ranch Hand

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

Lets say I have two tables:

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

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

Joined: Aug 22, 2010
Posts: 3733

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:
subject: SQL Nested select vs Outer Join
jQuery in Action, 3rd edition