Win a copy of Kubernetes in Action this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Nested select vs Outer Join  RSS feed

Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Posts: 3752
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!