Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 866
Neil Knight Avatar asked Feb 22 '11 10:02

Neil Knight


2 Answers

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)
like image 134
Matt Gibson Avatar answered Nov 15 '22 06:11

Matt Gibson


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)

like image 20
Damien_The_Unbeliever Avatar answered Nov 15 '22 07:11

Damien_The_Unbeliever