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
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
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
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