SQL with non-existing column in where cause can be executed

Here’s my SQL

select * 
from TableA 
where colA1 in (select colA1 from TableB where colB1 = 0)

The issue is, the colA1 does not exist in TableB.

So if I just run this

select colA1 
from TableB 
where colB1 = 0

Then SQL Server Management Studio will return an error:

Invalid column name ‘colA1’.

But if it is in the where cause, the first SQL can be executed as if where cause is always true.

Answer

Thats because SQL Server first looks for the column in TableB, fails to find it, so then looks in TableA. If you fully qualify your column names (or use table aliases) you will get the error e.g.

select A.*
from TableA A
where A.colA1 in (
    select B.colA1
    from TableB B
    where B.colB1 = 0
);

Essentially your query is reduced to:

select *
from TableA
where colA1 in (colA1);

Thats why you should always fully qualify your table names, ideally through the use of a short, meaningful aliases.