Writing JOINs vs Nested Select Queries
I have seen a lot of is queries written without using the JOIN syntax. Instead the developer chose to write the query using an IN clause in the WHERE clause with a SELECT statement; a Nested Query. While many people blame nested queries on developers that don't understand joins, they can actually be beneficial. Let's take a query out of the AdventureWorks DB.
An example would be:
SELECT Distinct C.AccountNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader O ON O.CustomerID = C.CustomerID
INNER JOIN Sales.SalesPerson SP ON SP.SalesPersonID = O.SalesPersonID
WHERE SP.CommissionPct >= .015
this can also written as
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE CommissionPct >= .015))
The second query is very messy to read and I personally hate looking at queries like this, but it is faster. To note I have an index on the SalesOrderHeader table with columns SalesPersonID, CustomerID. A distinct has to be used because multiple orders exist for a SalesPerson and Multiple Orders belong to multiple customers.
The following are the two execution plans.
Basically, the point is to not throw out a query because it looks messy. Every query must be analyzed for performance and different approaches should be looked at.
An example would be:
SELECT Distinct C.AccountNumber
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader O ON O.CustomerID = C.CustomerID
INNER JOIN Sales.SalesPerson SP ON SP.SalesPersonID = O.SalesPersonID
WHERE SP.CommissionPct >= .015
this can also written as
SELECT AccountNumber
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE CommissionPct >= .015))
The second query is very messy to read and I personally hate looking at queries like this, but it is faster. To note I have an index on the SalesOrderHeader table with columns SalesPersonID, CustomerID. A distinct has to be used because multiple orders exist for a SalesPerson and Multiple Orders belong to multiple customers.
The following are the two execution plans.
Basically, the point is to not throw out a query because it looks messy. Every query must be analyzed for performance and different approaches should be looked at.
This is great information, just what I was hoping for, has helped me with my coursework, thanks A+++++ Blogger!
Reply to this
Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
Reply to this
Hey friend I agree with your statement this was really helpful for me....
Reply to this
I ran the execution plan for the same queries in my SSMS and both are equal in cost. As well, i modified your top query to remove the DISTINCT keyword and replaced it with a GROUP BY clause. All three have the same cost.
Reply to this
I was just thinking about That Auction and you've really helped out. Thanks!
Reply to this
Really i am impressed from this post....the person who create this post he is a great human..thanks for shared this with us.i found this informative and interesting blog so i think so its very useful and knowledge able.I would like to thank you for the efforts you have made in writing this article
Reply to this
I got so many points here, that's why i love reading your post. Thank you so much!
Reply to this
Great post.
lalique
Reply to this
Thanks, nested queries in SQL is something I've found difficult to get my head around. These tips have been invaluable to me.
Reply to this
I ran the execution plan for the same queries in my SSMS and both are equal in cost. As well, i modified your top query to remove the DISTINCT keyword and replaced it with a GROUP BY clause.
Reply to this
Do you play multi-player online games on the cellphone?
Reply to this
can i get some easy step to understand the JOINs vs Nested Select Queries, please reply i am waiting for it,
thanks and regards to all.
Reply to this
I had a great time reading around your post as I read it extensively. Excellent writing! I am looking forward to hearing more from you.
Reply to this
I think this is more of an issue with your understanding of SQL than it is with iBATIS. See, the nested SQL statement is not “exposed” to the caller. It can only be used internally (except perhaps if you had put it in the FROM clause and subsequently included it in the SELECT clause of the “parent” statement...)So, I don’t think the question makes sense. You write the nested select like you would anywhere else. It doesn’t change the inputs (parameters) and outputs (select clause of the outermost statement) available for iBATIS to work with.
Reply to this
I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here! keep up the good work.
Reply to this
This page added to Google cache Cached: http://google.com/search?q=cache:http://mysqlserverblog.com/2007/11/01/writing-joins-vs-nested-queries.aspx?ref=rss&ei=AFQjCNHajN_OX0kgxzx7UGA1yBfPoRn TubedfWq
Reply to this
I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
Reply to this
Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
Reply to this
I like this concept. I visited your blog for the first time and just been your fan. Keep posting as I am gonna come to read it everyday!!
Reply to this
I wanted to thank you for this great read!! I definitely enjoying every little bit of it Smile I have you bookmarked to check out new stuff you post..
Reply to this
I havent any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
Reply to this
I must say that overall I am really impressed with this blog.It is easy to see that you are impassioned about your writing. I wish I had got your ability to write. I look forward to more updates and will be returning.
Reply to this
I just wanna thank you for sharing your information and your site or blog this is simple but nice article I've ever seen i like it i learn something today.
Reply to this
You made some good points there.I did a search on the topic and found most people will agree with your blog.Thanks
Reply to this
You got so many points here, that's why i love reading your post. Thank you so much!
Reply to this
since when i started to learn about the language i find more difficulties in nested as they confuse but from this site i got little knowledge about it.thank you
Reply to this
What are the five most popular sports in the United States?
Reply to this
This is my first time i have visited here. I found many interesting stuffs
Reply to this
Drug Rehab Center
Reply to this
This page added to Google cache Cached: http://google.com/search?q=cache:http://mysqlserverblog.com/2007/11/01/writing-joins-vs-nested-queries.aspx?ref=rss&ei=AFQjCNHajN_OX0kgxzx7UGA1yBfpoRn tubedfWq
Reply to this
Great post - Just subscriped to your RSS feed.. Thanks
Reply to this
Online Psychic Readings - 3 Minutes FREE! Astrology Psychics, Tarot Readings, Psychics, Free Psychic.
Reply to this
i will surely opt in for joins because nested queries are damn confusing and it takes a lot of time to think and write them down,on the other hand joins are so easy and you just need to use the on clause.
Reply to this
refreshing vacation replica kenneth cole Norelco razors are a convenience you will not want to be without Now cartier if you are used to wet shaving with a blade and shaving cream replica cartier watches you know just how messy this procedure is Norelco razors totally eliminate this mess and cleanup because the technology used in the design means you need no shaving cream to get a smooth shave without requiring a sink and running water No more cream omega replica foam gel dripping brush fake swiss army watches or watery mess to contend with as these razors do not need to give you a close and clean shave
Reply to this
Puh the post is a bit older but it is the first time that I see such a clear sheme! Thanks a bunch man!
Reply to this
Lender411.com is where you can shop for the lowest mortgage rates. Whether you are looking for fixed mortgage rates, variable adjustable mortgage rates (ARM), jumbo loans, interest only or even specialized mortgages such as bad credit mortgage or reverse mortgages, we will match you with up to 4 qualified lenders with 4 mortgage quotes.
Reply to this
Dennis Tubbergen
Reply to this