Column does not exist in the IN clause, but SQL ru

Question

I have a query that uses the IN clause. Here's a simplified version:

SELECT *
  FROM table A
  JOIN table B
    ON A.ID = B.ID
 WHERE B.AnotherColumn IN (SELECT Column FROM tableC WHERE ID = 1)

tableC doesn't have a Column column, but the query executes just fine with no error message. Can anyone explain why?

Answer 1

This will work if a table in the outer query has a column of that name. This is because column names from the outer query are available to the subquery, and you could be deliberately meaning to select an outer query column in your subquery SELECT list.

For example:

CREATE TABLE #test_main (colA integer) 
CREATE TABLE #test_sub (colB integer)

-- Works, because colA is available to the sub-query from the outer query. However,
-- it's probably not what you intended to do:
SELECT * FROM #test_main WHERE colA IN (SELECT colA FROM #test_sub)

-- Doesn't work, because colC is nowhere in either query
SELECT * FROM #test_main WHERE colA IN (SELECT colC FROM #test_sub)

As Damien observes, the safest way to protect yourself from this none-too-obvious "gotcha" is to get into the habit of qualifying your column names in the subquery:

-- Doesn't work, because colA is not in table #test_sub, so at least you get
-- notified that what you were trying to do doesn't make sense.
SELECT * FROM #test_main WHERE colA IN (SELECT #test_sub.colA FROM #test_sub)

Answer 2

If you want to avoid this situation in the future (that Matt Gibson has explained), it's worth getting into the habit of always using aliases to specify columns. E.g.:

SELECT *
  FROM table A
  JOIN table B
    ON A.ID = B.ID
 WHERE B.AnotherColumn IN (SELECT C.Column FROM tableC C WHERE C.ID = 1)

This would have given you a nice error message (note I also specified the alias in the where clause - if there wasn't an ID column in tableC, you'd have also had additional problems)