CODEWITHSUNDEEP
sql-servert-sql
Nick Sandel
Nick Sandel

Reputation: 112

SELECT NULL and NULL from OBJECT_ID seen differently for exists

I'd like to know why EXISTS behaves as if NULL is a result set when returned from OBJECT_ID() but not when you run SELECT NULL

I had briefly forgotten the usual syntax for checking if a table exists via OBJECT_ID so I came up with something similar to:

IF EXISTS (SELECT OBJECT_ID('clearlynotpresent'))
BEGIN
    PRINT 'Table exists'
    EXEC sp_rename @objname = 'clearlynotpresent', @newname = 'clearlypresent'
END
ELSE
    PRINT 'No table present'

Which wasn't working as I had thought. So I compared it to:

IF EXISTS (SELECT NULL)
    PRINT 'Null exists'
ELSE
    PRINT 'No null'

If you manually run the SELECT NULL and SELECT OBJECT_ID('clearlynotpresent') you get the same result set in SSMS but they behave differently in this context with EXISTS.

I have found the correct syntax for what I wanted now: IF (SELECT OBJECT_ID('clearlynotpresent')) IS NOT NULL

Upvotes: 0

Views: 184

Answers (2)

jlee-tessik
jlee-tessik

Reputation: 1520

What version of SQL Server are you using? Both of your examples come back with the exists statement for me (in 2012).

As for why this happens, the SELECT statement returns a value, but the value just happens to be null. Therefore, a value does exist and the clause works.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40309

In SQL 2008, I ran the following:

IF EXISTS (SELECT OBJECT_ID('Pages'))
    PRINT 'Table Pages exists'
ELSE
    PRINT 'No table present'

IF EXISTS (SELECT OBJECT_ID('xyz'))
    PRINT 'Table xyz exists'
ELSE
    PRINT 'No table present'

IF EXISTS (SELECT NULL)
    PRINT 'Null exists'
ELSE
    PRINT 'No null'

(table "Pages" was present, "xyz" was not) and got the following results:

Table Pages exists
Table xyz exists
Null exists

This makes sense to me, as exists checks if there is any data--that is, rows--present in the specified subset (what's in the parentheses). In all three cases there is one row of data; the fact that it's a single column containing a NULL integer is irrelevant.

Things might vary based on the version of SQL you are using, but that seems unlikely.

As per @JamesZ's comment, you're better off using 

IF object_id('xyz') is [not] null

Upvotes: 2

Related Questions