SubQuery invalid but SQL statement still executes?

Question

Post reply

SubQuery invalid but SQL statement still executes?

David1966a

SSC Rookie

Points: 37
More actions
January 7, 2005 at 2:39 am

#85043

In SQL Server 2000 when executing a statement of the following structure:
select ColumnA from TableA where ColumnB IN (select ColumnZ from TableB)
where ColumnZ does not exist in TableB, the SubQuery is ignored. I was expecting an exception to be raised.
Can anyone explain this because it is frustrating if you inadvertently add the wrong column to your subquery. Is this behaviour consistent with the latest SQL Server versions?
Thanks
David

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

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

~Sreejith G SSCrazy Points: 2311 More actions · Answer 1

This is quite interesting .... I would say plsssss check once again......... This will never happen...

David1966a SSC Rookie Points: 37 More actions · Answer 2

That's what I thought but I asked my colleagues to confirm it which they did.

Another interesting fact is that the behaviour is the same against an Oracle8 database but against Sybase an exception is raised.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Maybe building all the parts and then put them together is the route to go....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Antares686 SSC Guru Points: 125444 More actions · Answer 4

This sort of thing has been posted about many times before.

Instead of writing this way:

select ColumnA from TableA where ColumnB IN (select ColumnZ from TableB)

Do this:

select ColumnA from TableA where ColumnB IN (select TableB.ColumnZ from TableB)

The reason is that if ColumnZ exists in TableA but not TableB it will assume that is the ColumnZ you are refering to. Just make sure you qualify to be 100% sure it knows what you are looking for. The reason for this issues is the reason why you can do this and it is proper syntax and not a bug:

select ColumnA from TableA where EXISTS(select * from TableB WHERE TableB.ColumnZ = TableA.ColumnB)

David1966a SSC Rookie Points: 37 More actions · Answer 5

I agree that it is much better to use Exists but I am concerned that for this scenario the parser cannot recognise invalid columns in a Select ... From clause.

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Sorry you misunderstood. I didn't say Exists was better but for the same reason a column from table A can be referenced in the Exists Subquery it will do the same in and IN subquery. If you don't qualify the table name for the column and it is not found in the immediate table it will assume the outer table is where it is coming from. So if TableB has no ColumnZ but TableA does, no error will be thrown and the value of ColumnZ will be that from TableA. If you qualify the column with the table alias or name it will throw the error when the column does not exist.

~Sreejith G SSCrazy Points: 2311 More actions · Answer 7

Thats great peice of infrm....... I tried it in inner query & Sql is not picking it....