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

Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 3: Building a Correlated Subquery Expand / Collapse
Author
Message
Posted Monday, January 6, 2014 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
Comments posted to this topic are about the item Stairway to T-SQL Part 2: Beyond T-SQL Basics: Level 3: Building a Correlated Subquery

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1528100
Posted Monday, January 6, 2014 11:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
Thank you Gregory for this clear and concise article.

I cannot resist pitching in some Window function version of the queries;

;WITH CUST_SALES_DETAILS AS
(
SELECT
SSOH.CustomerID
,ROW_NUMBER() OVER
(PARTITION BY SSOH.SalesOrderID ORDER BY (SELECT NULL)) AS SSOH_RID
,COUNT(SSOD.SalesOrderDetailID) OVER
(PARTITION BY SSOH.SalesOrderID) AS COUNT_SSOD
FROM Sales.SalesOrderHeader SSOH
INNER JOIN Sales.SalesOrderDetail SSOD
ON SSOH.SalesOrderID = SSOD.SalesOrderID
)
SELECT
CSD.CustomerID
,CSD.COUNT_SSOD
/* ,DENSE_RANK() OVER (ORDER BY CSD.COUNT_SSOD DESC) AS SALES_RANK */
FROM CUST_SALES_DETAILS CSD
WHERE CSD.SSOH_RID = 1
AND CSD.COUNT_SSOD > 70;

GO
-------------------------------------------

;WITH CUST_SALE_BY_YEAR AS
(
SELECT
SSOH.CustomerID
,ROW_NUMBER() OVER
(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate])
ORDER BY (SELECT NULL)) AS CUST_RID
,YEAR(SSOH.[OrderDate]) AS SSOH_YEAR
,SUM(SSOH.SubTotal) OVER
(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate])) AS SSOH_SUBTOTAL
,(SUM(SSOH.SubTotal) OVER
(PARTITION BY SSOH.CustomerID,YEAR(SSOH.[OrderDate]))) * (0.10) AS SSOH_REBATE
FROM Sales.SalesOrderHeader SSOH
)
SELECT
CABY.CustomerID
/*
,DENSE_RANK() OVER (PARTITION BY CABY.SSOH_YEAR ORDER BY CABY.SSOH_SUBTOTAL DESC,CABY.SSOH_YEAR DESC) AS SALES_RANK
*/
,CABY.SSOH_YEAR
,CABY.SSOH_SUBTOTAL
,CABY.SSOH_REBATE
FROM CUST_SALE_BY_YEAR CABY
WHERE CABY.CUST_RID = 1
AND CABY.SSOH_YEAR = 2008
AND CABY.SSOH_SUBTOTAL > 150000;

GO
-------------------------------------------

Post #1528350
Posted Wednesday, March 5, 2014 1:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 78, Visits: 120
Thnks for the great article. I have just one remark :
For Question 1 the answer is C and not A. as the answer A refers to inner query instead of outer query.
Post #1547996
Posted Thursday, March 6, 2014 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:18 PM
Points: 48, Visits: 202
The correlated subquery may be executed many times. It will be run once for each candidate row selected in the outer query.


I believe that Jeff Moden refers to this as RBAR (row by agonizing row).

A nice followup article would be "How to avoid using the Correlated Subquery."
Post #1548224
Posted Thursday, March 6, 2014 1:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
Just to point out a possible improvement on the first query in part 3.
The sql engine has to match all rows from the subquery to the outer query. This is because the filtering is outside the subquery.

SELECT CustomerID FROM Sales.SalesOrderHeader OH
WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = OH.SalesOrderID) > 70;

Just by adding a GROUP BY clause, the work is cut by a 1/4, here are two suggestions, the latter slightly faster ;

SELECT CustomerID FROM Sales.SalesOrderHeader OH
WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = OH.SalesOrderID
GROUP BY SalesOrderID) > 70;

And

SELECT
SOH.CustomerID
FROM
(
SELECT
COUNT(*) AS SOD_COUNT
,SOD.SalesOrderID
FROM Sales.SalesOrderDetail SOD
GROUP BY SOD.SalesOrderID
) AS X
INNER JOIN Sales.SalesOrderHeader SOH
ON X.SalesOrderID = SOH.SalesOrderID
WHERE X.SOD_COUNT > 70;

Post #1548454
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse