Why does this query, missing a FROM clause, not error out?

This statement is legal (in other words, no FROM is required):

SELECT x = 1;
SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0;

The trick is when you introduce a column name that clearly can't exist. So these fail:

SELECT name WHERE 1 = 1;

SELECT x = 1 WHERE id > 0;

Msg 207, Level 16, State 1
Invalid column name 'name'.
Msg 207, Level 16, State 1
Invalid column name 'id'.

But when the invalid column is introduced in something like a subquery, what SQL Server does when it can't find that column in the inner scope of the subquery, is traverse to an outer scope, and make the subquery correlated to that outer scope. This will return all rows, for example:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE 1 = 1);

Because it's essentially saying:

SELECT * FROM sys.columns WHERE name IN (SELECT sys.columns.name WHERE 1 = 1); /*
              ^^^^^^^^^^^                       -----------
                   |                                 |
                   -----------------------------------    */

You don't even need a WHERE clause in the subquery:

SELECT * FROM sys.columns WHERE name IN (SELECT name);

You can see that it's really looking at the outer scoped table, because this:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE name > N'x');

Returns far fewer rows (11 on my system).

This involves adherence to the standard about scoping. You can see similar things when you have two #temp tables:

CREATE TABLE #foo(foo int);
CREATE TABLE #bar(bar int);

SELECT foo FROM #foo WHERE foo IN (SELECT foo FROM #bar);

Obviously, this should error, right, since there is no foo in #bar? Nope. What happens is that SQL Server says, "oh, I didn't find a foo here, you must have meant the other one."

Also, in general, I would avoid NOT IN. NOT EXISTS has the potential to be more efficient in some scenarios, but more importantly, its behavior doesn't change when it is possible that the target column could be NULL. See this post for more info.

I reproduced this in 2016 with a simplified example:

declare @t1 table (c1 int, c2 int, c3 int)
insert into @t1 values (1,2,3), (2,3,4), (3,4,5)

select * from @t1
where
    c1 not in 
    (select c2 where c3 = 3)

It appears that c2 and c3 are evaluated for each row.