Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Find the correct answer in the following query Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 3:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:26 PM
Points: 386, Visits: 622
Thanks for the explanation guys, I learned something today.
Actually 2 things. 1) scope and 2) yet another reason not to use subqueries



@kingston, It is a result but I would argue not the correct result. I can't imagine a scenario where you would write the query like that for the result described.

Personally I avoid subqueries whereever possible; I find they produce untidy code that is difficult to maintain and whereever possible I will use Common Table Expressions and then use an appropriate join in the main query. This has Three main benefits:

1) it keeps code clean
2) the same CTE is reusable a number of times
3) it preserves scope.

E.g Return all the details of the last order a customer placed.

SELECT
C.CustomerID,
O,*
FROM
Customer C
JOIN
(/* Last Order For Each Customer*/
SELECT CustomerID,max(orderID) from Orders O Group by O.CustomerID
) LO on LO.CustomerID = C.CustomerID
JOIN
Orders O on O.OrderID = LO.OrderID

Compared to
WITH CTE_LastOrder as
(/* Last Order For Each Customer*/
SELECT CustomerID,max(orderID) from Orders O Group by O.CustomerID
)

SELECT
C.CustomerID,
O,*
FROM
Customer C
JOIN
CTE_LastOrder LO on LO.CustomerID = C.CustomerID
JOIN
Orders O on O.OrderID = LO.OrderID

I find the second easier to read and maintain. When looking at the main code I don't have to rethink what the subquery is doing each time, I can get the gist from the suitably named CTE. I can fiddle with the CTE and test the code independently of the main query. When building very complex queries, you can build a series of CTEs that build upon one another and re-use them.

anayway I have gone WAAAAAY off topic. We should really answer the OPs question which is 100: All the customers in the customer list.
Post #1465541
Posted Thursday, June 20, 2013 3:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
aaron.reese (6/20/2013)
Thanks for the explanation guys, I learned something today.
Actually 2 things. 1) scope and 2) yet another reason not to use subqueries


Correlated subqueries can be incredibly powerful, they're not something to avoid, just to understand.

It is a result but I would argue not the correct result. I can't imagine a scenario where you would write the query like that for the result described.


The query is likely written incorrectly, however the results are correct for the way it's written.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1465547
Posted Thursday, June 20, 2013 3:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:46 AM
Points: 18, Visits: 157
Ya i created and saw the result.

Thanks all
Post #1465553
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse