Querying columns that don't exist, doesn't fail

Question

Strange situation.

If I have these tables:

CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
GO
CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
GO

and do:

SELECT    t1.* 
FROM    t1 
WHERE    t1.id IN (SELECT someIntCol FROM t2)

weird thing: the parser doesn't mind that the column someIntCol doesn't exist

weirdest thing: if I change someIntCol to someIntCol2, I get a "Invalid column name 'someIntCol2'." error

Can anyone explain that?

FYI, this is not my code. I got it from this link

Answer 1

Try:

SELECT    t1.* 
FROM    t1 
WHERE    t1.id IN (SELECT t2.someIntCol FROM t2)

This will now throw an exception when executed.

Because someIntCol exists in t1, it is using that data item from the main query.

Sub-queries are able to use data items from the main query. Therefore to avoid this, specify the table name when entering your data item:

SELECT [TableName].[ColumnName]

This will also prevent ambiguity if you have 2 columns with the same name in t1 and t2

---

Heres an MSDN which may give you a better understanding of how subqueries work:

http://msdn.microsoft.com/en-us/library/aa213262(v=sql.80).aspx