Correlated Subquery?

Question

Correlated Subquery?

SQLSACT

SSC-Insane

Points: 21838
More actions
January 9, 2015 at 3:42 am

#313500

Hi all
I came across the following T-SQL query in an article I was reading.
Can someone please explain what type of query this is?
Is this some type of correlated subquery?
SELECT sku, product_description,
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246944 More actions · Answer 1

SQLSACT (1/9/2015)
Hi all
I came across the following T-SQL query in an article I was reading.
Can someone please explain what type of query this is?
Is this some type of correlated subquery?
SELECT sku, product_description,
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;
Thanks

Yes it is, because the inner subquery includes a reference to a column in the main outer query.

It's a weird one, because it's much simpler to write

select p.sku

,p.product_description

,pp.plant_nbr

from dbo.Products p

join dbo.ProductPlants pp on p.sku = pp.sku

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Thanks

Is it the same as:

select p.sku

,p.product_description

,pp.plant_nbr

from dbo.Products p

where exists (select pp.sku from dbo.ProductPlants pp where p.sku = pp.sku)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SQLSACT (1/9/2015)
Thanks
Is it the same as:
select p.sku
,p.product_description
,pp.plant_nbr
from dbo.Products p
where exists (select pp.sku from dbo.ProductPlants pp where p.sku = pp.sku)

No. What would you return if there were multiple matches in the subquery?

I find this more intuitive:

select p.sku

,p.product_description

,plant_nbr = CAST(NULL AS INT)

from dbo.Products p

where exists (select 1 from dbo.ProductPlants pp where p.sku = pp.sku)

Edited: removed unavailable column 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, that won't even run.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

GilaMonster (1/9/2015)
No, that won't even run.

Because of the columns in my select list?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Try it and see?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

SQLSACT

SSC-Insane

Points: 21838

January 9, 2015 at 5:17 am

#1769033

GilaMonster (1/9/2015)
Try it and see?

Yep

ScottPletcher SSC Guru Points: 101078 More actions · Answer 8

SQLSACT (1/9/2015)
SELECT sku, product_description,
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku = A.sku) AS plant_nbr
FROM Products AS A;
Thanks. Is it the same as:
select p.sku
,p.product_description
,pp.plant_nbr
from dbo.Products p
where exists (select pp.sku from dbo.ProductPlants pp where p.sku = pp.sku)

Not at all. Two fundamentally different queries.

The first query will return all rows from Products even if it finds no matches in ProductPlants -- any row that doesn't have a match will just return NULL instead of an actual plant_nbr. And the query will fail if more than one matching rows exists in ProductPlants, since a full table result cannot be returned into a single column that way.

The second query would only return rows that find a match in ProductPlants, but multiple matches will not cause a problem, except that it can't run because "pp" is in an EXISTS() subquery, and thus is not available to, and thus "can't be seen by", the outer query.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".