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

So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?

SELECT

    1
   ,r.id
   ,'0D4133BE-C1B5-4141-AFAD-B171A2CCCE56'
   ,GETDATE()
   ,1
   ,'Y'
   ,'N'
   ,oldItem.can_view
   ,oldItem.can_update

FROM Role r

JOIN RoleObject oldReport
    ON r.customer_id = oldReport.customer_id

JOIN RoleItem oldItem
    ON oldReport.id = oldItem.role_object_id
        AND r.id = oldItem.role_id

WHERE r.id NOT IN (SELECT
        role_id
    WHERE role_object_id = '0D4133BE-C1B5-4141-AFAD-B171A2CCCE56')

AND oldReport.id = '169BA22F-1614-4EBA-AF45-18E333C54C6C'

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.

StackExchange Database Administrators Q#240068, answer score: 20