Interesting non-error using WHERE with NOT IN

Question

Post reply

Interesting non-error using WHERE with NOT IN

anthonymendozajr

SSC Enthusiast

Points: 199
More actions
December 7, 2015 at 6:49 pm

#302789

I came across an interesting non-error in SQL Server 2014 EE with a where condition that uses a not in... The field name in the select statement within the not in is invalid. The correct field name is Feed_Name but FeedName is in the statement. The statement with the not in will run w/o error and return 0 results. If I run the statement alone it will produce an error. This is a little concerning for me and I am curious if anyone super smart knows why???
--Query with incorrect field name in NOT IN
SELECT FeedName ,
'Missing FileTableDB Record - External Feed' AS Alert_Type
FROM Alert.Export_Files
WHERE FeedName NOT IN ( SELECT DISTINCT
FeedName
FROM FileTableDB.dbo.External_Feed_Lookup );
GO
SELECT DISTINCT
FeedName
FROM FileTableDB.dbo.External_Feed_Lookup;
GO

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

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

I assume FeedName is the name of a column in Alert.Export_Files.

Check out the cautionary notes here: https://technet.microsoft.com/en-us/library/ms178050(v=sql.105).aspx

This is a common source of confusion, and an excellent reason to explicitly qualify column names with table names or aliases.

Cheers!

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 2

If I were a betting man, I'd put my $$$ on Jacob being correct... Lazy/sloppy coding can & will come back to bite you in some pretty surprising ways.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

anthonymendozajr (12/7/2015)
This is a little concerning for me and I am curious if anyone super smart knows why???

Because it's perfectly legit SQL, syntatically valid. The fact that it's not what you want is irrelevant. The parser can't read your mind. 🙂

The binding order for columns within a subquery is first to tables within the subquery. If no tables within the subquery have the column, then the binding is to tables outside. This is why, when you're using IN, EXISTS or their negations, you should always, always, always, always qualify your column names.

CREATE TABLE T1 (Col1 INT)

CREATE TABLE T2 (Col2 INT)

SELECT Col1 FROM T1 WHERE Col1 IN (SELECT Col1 FROM T2)

That is perfectly valid. The binding order means that it is essentially

SELECT Col1 FROM T1 WHERE Col1 IN (SELECT T1.Col1 FROM T2)

Which is probably not what was intended, but is perfectly valid.

If you qualify all columns all the time, then

SELECT Col1 FROM T1 WHERE T1.Col1 IN (SELECT T2.Col1 FROM T2)

will throw an error.

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

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

Jason A. Long (12/7/2015)
If I were a betting man, I'd put my $$$ on Jacob being correct

You don't have to bet. From the OP:

SELECT FeedName ,

'Missing FileTableDB Record - External Feed' AS Alert_Type

FROM Alert.Export_Files

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

anthonymendozajr SSC Enthusiast Points: 199 More actions · Answer 5

Ahhhh... That makes sense.... I spaced on noticing the field name was valid in the select statement. Thanks for the link Jacob and the in depth play-by-play Gail.