Query works although subquery produces an error

Question

Query works although subquery produces an error

flinzer

SSC Veteran

Points: 295
More actions
January 24, 2011 at 1:52 am

#388697

I found an interesting behaviour in SQL Server 2008:
I have a query like this: (I know it could be written better, but some colleague did this :-))
select count(*)
from dbo.Table1 t1
where t1.Field1 in
(select Field2 from dbo.Table2 where Field3 = 1)
The problem now is:
dbo.Table2 does not contain the Field2, the subquery, executed isolated, simply produces an error.
But: if I execute the whole query, it runs without any error and returns all rows of dbo.Table1, just the way if I hadn't had the where-clause at all!
Is there some DB-Option or anything else which I don't know?
Well, the colleague managed to delete all rows in a table using this syntax...

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

select count(*)
from dbo.Table1 t1
where t1.Field1 in
(select Field2 from dbo.Table2 where Field3 = 1)
[/code]

Its because of your in operator. Which means if its in the list then ok else return whatever remaining.

For example, If you run this query :-

select * from sysjobs s1 where s1.name in

(select name from sys.databases where name = 4)

you will see 0 result but no error. And if you run the subquery you will get the error as 'conversion failed..........................'

hope it clears your doubt.

----------
Ashish

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

Nothing unusual about this at all.

When you have a subquery it's completely valid to reference columns from the outer table inside the subquery. If it wasn't it would be impossible to write correlated subqueries.

The reference to Field2 will only throw an error is there is no column within the current scope that matches that name. There is such a column, it's just not in the table you thought it was.

This is another reason why it is recommended to qualify all columns with the table names. The following would throw an error.

select count(*)

from dbo.Table1 t1

where t1.Field1 in

(select t2.Field2 from dbo.Table2 t2 where t2.Field3 = 1)

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

flinzer SSC Veteran Points: 295 More actions · Answer 3

Thanks for your replies.

Didn't know about this, but one never stops learning new things 🙂