I apologize for this not being specific to SQL 2012, but rather SQL in general, I ran into an issue with a statement that I expected would throw an error and yet it runs in an unexpected way. Basically the subquery should select a column that does not exist in the table referenced in it, yet it somehow runs. Below are the steps to reproduce:
create table test1 (firstname1 varchar(10), lastname1 varchar(10))
create table test2 (firstname2 varchar(10), lastname2 varchar(10))
insert test1 values ('Larisa', 'Brown')
insert test2 values ('John', 'Chaplan')
select * from test1
where firstname1 in (select firstname1 from test2)
Even though there is no column firstname1 in table test2, this query returns all rows in the table test1 and works as long as the subquery uses a column name defined in the first table and the second table is not empty. Prepending the proper table name to the subquery column (table2.firstname1) returns an error as expected, however why doesn't it return an error without it?