A weird result from IN (SELECT

I was recently building a query that referenced a SQL Server 2000 database on a linked server. I’m using SQL Server 2008 R2 on my end and I’m used to Intellisense suggesting the columns in the table I’m querying. Unfortunately, it doesn’t make these suggestions for the linked server in question, but I thought I remembered the columns so I just built the query without checking.

For example I had a local table

CREATE TABLE DailySales(
SaleID int NOT NULL,
SaleDesc varchar(100) NOT NULL,
TenderType int NOT NULL,
Amount smallmoney NOT NULL
)

and I wanted to see if the sales IDs existed in another table on another server

CREATE TABLE SalesLoad(
Sale_ID int NOT NULL,
Sale_Desc varchar(100) NOT NULL,
Load_Date datetime NOT NULL
)

I built my query

SELECT SaleID, SaleDesc, TenderType, Amount
FROM DailySales
WHERE SaleID IN (SELECT SaleID FROM OldServer.DBName.dbo.SalesLoad WHERE Load_Date = ’6/1/2011′)

The table DailySales had 32,450 records. The number of matching records on SalesLoad were 560. How many records did I see returned?

32,450

At first I was very perplexed, the query must have been a valid one, it returned records! Why was it returning every record in the local table?
The problem is that there’s an error in the IN (SELECT. It should have read

SELECT Sale_ID FROM OldServer.DBName.dbo.SalesLoad WHERE Load_Date = ’6/1/2011′

But I’d left out the underscore in the column name. Instead of failing and telling me the query failed, it returned every record in the local table. If there’s no data in the table referenced in the IN clause, no records are returned either way, but if there’s data there, it’ll return everything in the parent table without letting you know anything is wrong with your query. Yet another reason it is important to have an idea of what should be returned from a query, so you don’t just assume whatever is returned is correct because data came back.

If you highlight the query in the IN clause and try to run it by itself, you’ll get an error telling you your column name is wrong. I tried it again later to see if the same would hold true if both tables were on the same server and it does! In fact, it won’t tell you the column is wrong even if you have Intellisense on. It may consider it a valid column because that column is in the other table, but Intellisense won’t flag it as a mistake. I’m not sure if the query returns everything in the main table because it just ignores the IN clause or if it sees the SaleID column name and cross references back to the table in the main query. I should try it on a table with a non-unique column and see if I get results like in a CROSS APPLY. That should let me know which of those scenarios is true.

This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>