Need help understanding Correlated Subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help understanding Correlated Subqueries

Author

Topic

hellfire45
Starting Member

10 Posts

Posted - 2015-03-07 : 22:37:31

I've been taking an online video course but without somebody to help me with questions, I'm having a bit of trouble understanding the concept of a Correlated Subquery.

In the code below, the concept makes sense to me, for every productID its taking an average of the linetotal, however the fact that it does this for every single productID stumps me. I don't just want to understand the result but understand the process that the code follows.

Can somebody help step me through the linear process of how this code runs? If I can see it step by step, I think I'd understand better.

Thanks. I'm quite new at this. Just trying my best!

SELECT salesorderdetailID, productID, orderQTY, linetotal 
FROM SalesLT.SalesOrderDetail s1
WHERE linetotal > 

(SELECT AVG(linetotal) from SAlesLT.SalesOrderDetail S2
WHERE s1.productID = s2.productID)

hellfire45
Starting Member

10 Posts

Posted - 2015-03-07 : 22:52:23

I got the code below from TechNet and it makes a little more sense. Correct me if i am wrong but its saying, give me data where 5000 is in the BONUS column amongst the rows where the businsessentityID's match between SP and E. So basically, first it cuts the query result down to the rows that match between SP and E, then from these, it takes the ones where 5000 is in the bonus column? but no...then couldn't you just use a simple join statement where with an added WHERE?

However, in the result above, it seems more confusing. It's like narrowing it down to the Product ID's that match between S1 and S2 but how does it know to that a seperate average for EVERY productID that matches in the join? It feels like it would just find all the matching IDs and then average them all together. Hmm...

USE AdventureWorks2008R2;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-08 : 09:47:49

Yes. Yes. Yes.

The example is a poor example (imho). I'd prefer to see a second join as you suggest

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources