how to explain the correlated subquery

Question

how to explain the correlated subquery

viveksvkola

SSCrazy

Points: 2228
More actions
December 5, 2012 at 7:50 pm

#285903

Hi All,
I want to know how to explain the correlated subquey like "SELECT * FROM CUSTOMER A WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM CUSTOMER B WHERE A.SAL<=B.SAL)".
Pls any one can explain.
Thanks,
Vivek.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

viveksvkola (12/5/2012)
Hi All,
I want to know how to explain the correlated subquey like "SELECT * FROM CUSTOMER A WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM CUSTOMER B WHERE A.SAL<=B.SAL)".
Pls any one can explain.
Thanks,
Vivek.

It's simply an INNER JOIN that can't or shouldn't necessarily be done in the FROM clause of the outer query (although this one certainly could).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mickey Stuewe SSC Eights! Points: 879 More actions · Answer 2

It's a query, within a query. In the example you gave, the inner query would be executed first to get a value that can then be used in the outer query. In the example below, the inner querry is executed for each parentID to obtain the count of children the parent has.

SELECT

p.LastName

,p.FirstName

,cc.ChildrenCount

FROM

Parent AS p

CROSS APPLY

(

SELECT

COUNT(ChildID) as ChildrenCount

FROM

Children AS c

WHERE

p.ParentID = c.ParentID

) as cc

WHERE

cc.ChildrenCount > 3

Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.