Sub select anomaly

[TysonLPrice]

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 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

 

[gmmastros]

Ah.... the subquery. Personally, I don't like them. This example clearly shows a misconception about subqueries. I think the problem is the parenthesis. People have a tendency to think the what is inside the parenthesis is a stand alone thing. With a subquery, this is not true. With a derived table, it is true.

For example, consider the following.

Declare @Temp Table(SSN Varchar(10))

insert into @Temp(SSN) values('123456780')
insert into @Temp(SSN) values('123456788')
insert into @Temp(SSN) values('123456787')
insert into @Temp(SSN) values('123456786')
insert into @Temp(SSN) values('123456785')

Select * 
From   @Temp
Where  SSN in ([!]Select 1[/!])

The query above parse correctly, but returns 0 rows.

Now consider this.

Declare @Temp Table(SSN Varchar(10))

insert into @Temp(SSN) values('123456780')
insert into @Temp(SSN) values('123456788')
insert into @Temp(SSN) values('123456787')
insert into @Temp(SSN) values('123456786')
insert into @Temp(SSN) values('123456785')

Select * 
From   @Temp
Where  SSN in ([!]Select SSN[/!])

This query returns all of the rows.

Now, it seems as though you are expecting just one row from the #Select table based on rows that exist in the SubSelect table. In a case like this, I would have written it as an inner join query.

Select #Select.*
From   #Select
       Inner Join #SubSelect
         On #Select.SSN = #SubSelect.XXX

The query shown above returns the one row like you expect.

If you want to use the IN syntax (I don't recommend it), you should do something like this.

select SSN from #Select where SSN in (Select SSN from #SubSelect[!] where #Select.SSN = #SubSelect.XXX[/!])

If I am not mistaken, using exists is better than using in (for performance reasons). so...

select SSN from #Select where [!]exists[/!] (Select SSN from #SubSelect where #Select.SSN = #SubSelect.XXX)

Lastly, take a look at the last 2 queries. Notice how there is a #Select.SSN within the parenthesis? This clearly indicates that there is nothing magical about the parenthesis. Specifically, you can reference things inside the parenthesis that appear to only exist outside of it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Someone pointed me to a link with an explanation...

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

 

[tsuji]

[0] The way to comprehend/rationalize the output can be this.
[0.1] When #subselect is empty, this query would return null.
[tt] select '123456780' from #subselect[/tt]
or this if that is not arbitrary enough
[tt] select 1 from #subselect[/tt]
[0.2] When #subselect is no longer empty, these are all legitimate.
[tt] select '123456780' from #subselect[/tt]
[tt] select 1 from #subselect[/tt]
and return '123456780' and 1 respectively.
[0.3] Hence, putting it into a subquery situation, each SNN will be a literal string ('123456780', then '123456788, then ...) when the subquery is being executed. Hence SSN in ('123456780') when actually SSN is in fact '123456780', etc. Hence, the observation.

[1] If it is not SSN but some name "abc" (anything other than the column name XXX) at its place in the subquery, it would be an error as anticipated.

 

[tsuji]

Sorry, way too slow, haven't seen all the responses before mine.