Sub select anomaly

Question

Post reply

Sub select anomaly

tyson.price

SSCrazy

Points: 2093
More actions
October 27, 2010 at 3:53 am

#225567

Not really a question. I made a mistake on a column in a sub select and was surprised the compiler didn't catch it. Seems like a bug to me. At the very least I wouldn't think the second select would bring back anything rather then all the rows.
create table #Select(SSN Varchar(10))create table #SubSelect(XXX Varchar(10))insert into #Select(SSN) values('123456780')insert into #Select(SSN) values('123456788')insert into #Select(SSN) values('123456787')insert into #Select(SSN) values('123456786')insert into #Select(SSN) values('123456785')-- This does not return any rowsselect SSN from #Select where SSN in (Select SSN from #SubSelect)insert into #SubSelect(XXX) values('123456780')-- This returns all rows. -- I would have suspected the compiler would have flagged that SSN is not a column-- in #SubSelectselect SSN from #Select where SSN in (Select SSN from #SubSelect)Drop table #SelectDrop table #Subselect
edit:
I used code tags and it put it into a control like a slider versus showing the code. What should I be using?

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

That is the expected behaviour and follows the SQL ANSI standards on sub-selects.

It is strange but it is worth knowing as it catches people out.

more detail here

http://onlamp.com/pub/a/onlamp/2002/08/22/gennick.html"> http://onlamp.com/pub/a/onlamp/2002/08/22/gennick.html

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Here's your code formatted properly.

CREATE TABLE #Select (SSN VARCHAR(10))

CREATE TABLE #SubSelect (XXX VARCHAR(10))

INSERT INTO #Select (SSN)

VALUES ('123456780')

INSERT INTO #Select (SSN)

VALUES ('123456788')

INSERT INTO #Select (SSN)

VALUES ('123456787')

INSERT INTO #Select (SSN)

VALUES ('123456786')

INSERT INTO #Select (SSN)

VALUES ('123456785')

-- This does not return any rows

SELECT SSN

FROM #Select

WHERE SSN IN (SELECT SSN

FROM #SubSelect)

INSERT INTO #SubSelect (XXX)

VALUES ('123456780')

-- This returns all rows.

-- I would have suspected the compiler would have flagged that SSN is not a column

-- in #SubSelect

SELECT SSN

FROM #Select

WHERE SSN IN (SELECT SSN

FROM #SubSelect)

DROP TABLE #Select

DROP TABLE #Subselect

As for your question, the "SSN" column used in the subquery is the one from the outer query (#Select table). When a column cannot be resolved locally, the parser tries to resolve it within all other contexts available.

-- Gianluca Sartori