If Exists behaves differently depending on column or table missing

Question

Im trying to understand why this code, in my opinion, behaves inconsistently. I have a simple if exists clause which tests if a column is present in a table or not.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'COL' and TABLE_NAME = 'TAB')
BEGIN
    PRINT 'EXISTS'
    SELECT COL FROM TAB
END
ELSE
BEGIN
    PRINT 'NOT EXISTS'
END

This executes just fine in case table TAB has the column COL. If the column COL gets dropped from TAB the statement can not be executed stated invalid column. So far so good. Then if i drop the table TAB altogether the statement is executed just fine and printing "NOT EXISTS". I´ve tested it further and it seems as long as the table does not exist and IF EXISTS is evaluated to false you can write whatever you want for example:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE 1=0)
BEGIN
    PRINT 'EXISTS'
    SELECT THISISMYFANTASYCOLUMN FROM FANTASYTABLE
END
ELSE
BEGIN
    PRINT 'NOT EXISTS'
END

This statement always prints 'NOT EXISTS' and sqlserver is not complaining.

EDIT: To differentiate from the possible duplicate question. I´m not looking for a solution to this problem which is to use dynamic sql. The question is why existing table + invalid columnname raises an error but non existing table with non existing column is valid. Seans example illustrates the different behaviour more clearly than the original posting.

This is because the query inside the IF statement is only parsed for syntax accuracy. The error is a runtime error and throws the exception during execution. You can verify this by parsing your query in SSMS (the blue check mark button). — Sean Lange, Sep 19 '19 at 18:31
@SeanLange but why doesn't it throw a "table does not exist" error when the table doesn't exist? I would expect it to. — Tab Alleman, Sep 19 '19 at 18:43
Perhaps you can elaborate further? In both cases if exists is evaluated to false so only the else case is executed. In this case i don´t understand why SELECT NOTEXISTINGCOL FROM NOTEXISTINGTAB is valid but SELECT NOTEXISTINGCOL FROM EXISTINGTAB raises an error. — John, Sep 19 '19 at 18:43
Indeed -- I have tried to replicate what you have done and I never get any errors. — avery_larry, Sep 19 '19 at 18:47
@SeanLange yeah but in the second query if you exchange FANTASYTABLE with a real table you cant execute it anymore. — John, Sep 19 '19 at 18:57
@avery_larry I get the error when the SELECT statement under "Exists" contains a table that exists, but a column that does not exist in that table. — Tab Alleman, Sep 19 '19 at 19:00
I think the second query in the OP is confusing the issue. It is really two different versions of the first query. One with a valid table but an invalid column name causes a compile error. With an invalid table name it becomes a run time error. Quite strange indeed. It is sort of behaving a little bit like deferred name resolution. Maybe there is a bit of a quirk from that here? — Sean Lange, Sep 19 '19 at 19:01
@avery_larry write an exists query which is always evaluated to false like (Select * from XXX where 1 = 0). — John, Sep 19 '19 at 19:02
Yes, apparently the pre-parser only throws an error if the table exists. If it doesn't exist, it leaves it for the run-time parser. It may be useful to the OP to know that this has nothing to do with what's in the IF EXISTS. It has to do with how the pre-parser performs semantic checks. If this behavior is documented anywhere, however, I have not been able to find it. — Tab Alleman, Sep 19 '19 at 19:03
I don't know about that. For instance, you can change the select statement to: select 5/0 and it doesn't throw an error unless the if exists returns true. — avery_larry, Sep 19 '19 at 19:05
And it doesn't throw an error with an invalid table. Only an error with an invalid column in an existing table. — avery_larry, Sep 19 '19 at 19:06
I don't think division by zero is an error the pre-parser would look for. I think it's only a run-time error. — Tab Alleman, Sep 19 '19 at 19:09
For the second one, the IF is always evaluated to false (WHERE 1=0). The optimizer is smart enough to remove the code branch. — PeterHe, Sep 19 '19 at 19:33
@PeterHe the optimizer is not that smart. Inside an exists where 1 = 0 if you reference an invalid column for an existing table it will throw an error. — Sean Lange, Sep 19 '19 at 19:37
IF you check the Execution plan, you only see the IF EXISTS. Everyting, including the SELECT THISISMYFANTASYCOLUMN FROM FANTASYTABLE, in the if branch is eliminated. — PeterHe, Sep 19 '19 at 20:01
When the IF is complied and executed, and evaluated to FALSE, the branch is not complied at all. — PeterHe, Sep 19 '19 at 20:10
Possible duplicate of [T-Sql appears to be evaluating "If" statement even when the condition is not true](https://stackoverflow.com/questions/19822843/t-sql-appears-to-be-evaluating-if-statement-even-when-the-condition-is-not-tru) — avery_larry, Sep 19 '19 at 20:32
Some things seem to be compiled and bound at batch parsing time, and some things seem to be compiled and bound at run time. I've always found that tables seem to be run-time bound, but columns are compile-time bound. It's very odd and, imho, inconsistent. When creating SQL to run across versions where a table may or may not exist, it's easy. When a column may or may not exist, I always have to resort to dynamic SQL. — pmbAustin, Sep 19 '19 at 20:49
@avery_larry its kind of similar question. To avoid the problem altogether better use dynamic sql. But the question specifically is why sql-server is treating columns more strictly than tables. Sean Lange boiled the issue down to just two lines to illustrate the different behavior. After years working with sql-server i find this behaviour quite odd and unintuitive hence the question. If there is a technical explanation for this behaviour i would love to read about it if such documentation exists. — John, Sep 19 '19 at 23:19
Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/199796/discussion-on-question-by-john-if-exists-behaves-differently-depending-on-column). — Samuel Liew, Sep 22 '19 at 09:15

score 2 · Answer 1 · answered Sep 19 '19 at 19:20

This isn't really an answer but is a simpler example of demonstrating the behavior. The EXISTS statement is adding a layer of complexity that is confusing people.

When executing the following code you will see the PRINT executes. This is for a table that doesn't exist.

PRINT 'EXISTS'
SELECT name FROM sys.databasesasdf

However if you reference a column that doesn't exist in an existing table the PRINT does not execute.

PRINT 'EXISTS'
SELECT asdf FROM sys.databases

If Exists behaves differently depending on column or table missing

1 Answers1