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!?



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 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.


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
    c1 not in 
    (select c2 where c3 = 3)

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


In SQL Server SELECT syntax do not require FROM section. If you omit FROM, select statement will use "dummy" table which has one row and no columns. So

select 'x' as c where ...

will return one row if the expression is true and no rows when it is false.

  • But that doesn't work if you just say select c and c does not exist in some outer object. I agree that FROM is not required, but the mechanics in play here when you explicitly name a column that does exist in an outer scope are definitely different than a dummy table, and if you don't provide a constant for a column that doesn't exist, you get a runtime error, so no dummy table there either. Dummy table can come into play in other scenarios, but not when the reference is in a subquery / derived table. – Aaron Bertrand Jun 7 '19 at 16:21
  • In you example it is a correlated sub-select, role_id and role_object_id belongs to one of the tables in the outer select. – Piotr Jun 7 '19 at 16:23
  • Right, but saying SELECT 'x' AS c is a completely different scenario than the OP's, who just said SELECT c. In a subquery / derived table. – Aaron Bertrand Jun 7 '19 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.