SQL IN query produces strange result

Question

See table below:

CREATE TABLE Person (id int not null, PID INT NOT NULL, Name VARCHAR(50)) CREATE TABLE [Order] (OID INT NOT NULL, PID INT NOT NULL) INSERT INTO Person VALUES (1,1,'Ian') INSERT INTO Person VALUES (2,2,'Maria') INSERT INTO [Order] values (1,1) 

Why the following query returns two results:

 select * from Person WHERE id IN (SELECT ID FROM [Order]) 

ID does not exist in the order. Why does the above query produce results? I would expect this to be a mistake, because I would not be fine.

Answer 1

The problem is that you are not using the Table.Column notation in your subquery, the Order table does not have an ID column and the ID in the subquery really means Person.ID , not [Order].ID . Therefore, I always insist on using aliases for tables in production code. Compare these two queries:

 select * from Person WHERE id IN (SELECT ID FROM [Order]); select * from Person as p WHERE p.id IN (SELECT o.ID FROM [Order] as o) 

The first will execute, but will return incorrect results, and the second will cause an error. This is because in the subquery you can refer to the outer columns of the query, so in this case you can use the Person columns inside the subquery. Perhaps you wanted to use a query like this:

 select * from Person WHERE pid IN (SELECT PID FROM [Order]) 

But you never know when the layout of the [Order] table changes, and if someone removes the PID column from [Order] , then your query will return all rows from the Person table. Therefore, use aliases:

 select * from Person as P WHERE P.pid IN (SELECT O.PID FROM [Order] as O) 

Just a quick note is not SQL Server specific behavior; it is standard SQL:

• SQL Server Demo
• PostgreSQL Demo
• MySQL demo
• Oracle Demo

Answer 2

This behavior, although unintuitive, is very well defined in the Microsoft Knowledge Base:

KB # 298674: PRB: Subquery resolves column names for external tables

From this article:

To illustrate the behavior, use the following two table structures and query:

 CREATE TABLE X1 (ColA INT, ColB INT) CREATE TABLE X2 (ColC INT, ColD INT) SELECT ColA FROM X1 WHERE ColA IN (Select ColB FROM X2) 

The query returns a result in which the ColB column is viewed from table X1.

When qualifying the column name, an error message appears, as shown in the following query:

 SELECT ColA FROM X1 WHERE ColA in (Select X2.ColB FROM X2) 

Server: msg 207, level 16, state 3, line 1
Invalid column name 'ColB'.

People have been complaining about this problem for many years, but Microsoft is not going to fix it. This, in the end, is consistent with the standard, which essentially states:

If you do not find column x in the current area, go to the next outer area and so on until you find the link.

For more information, see the following “Error” messages, as well as some official evidence that this design behavior will not change (so you have to change yours — that is, always use aliases ):

Connection # 338468: CTE column name resolution in Sub Query not checked
Connect # 735178: T-SQL subquery does not work in some cases when the IN statement is used
Connection # 302281: non-existent column ignores subquery
Connection # 772612: an alias error is not reported when inside an IN statement
Connect # 265772: Error using sub-block selection

In your case, this “error” is likely to be much less likely if you use more meaningful names than ID, OID, and PID. Does Order.PID to Person.id or Person.PID ? Create your spreadsheets so people can figure out relationships without asking you. A PersonID should always be PersonID , regardless of where it is in the schema; same with OrderID . Saving multiple input characters is not a good price to pay for a completely ambiguous scheme.

Instead, you can write an EXISTS clause:

 ... FROM dbo.Person AS p WHERE EXISTS ( SELECT 1 FROM dbo.[Order] AS o WHERE o.PID = p.id -- or is it PID? See why it pays to be explicit? ); 

Answer 3

There is no id column in the order table

Try instead:

 select * from Person WHERE id IN (SELECT OID FROM [Order]) 

OR

 select * from Person WHERE pid IN (SELECT PID FROM [Order])