SEO

#1 (**permalink**) 02-29-2008, 07:27 AM

In T sql for sql server, what is the technical difference between the
comparisons "is" and "="
for example:
set @test = null

print @test is null -> true
print @test = null -> false

#2 (**permalink**) 02-29-2008, 07:27 AM

In SQL, NULL represents a missing/unknown value. The basic comparison
operators like =, <>, <, >, IN, etc return an UNKNOWN result when NULLs are
compared. IS NULL / IS NOT NULL are special types of comparison for
verifying the presence or absence of NULLs.

Read about NULLs and three-value logic in Books Online:
http://msdn.microsoft.com/libr*ary/e...d_02_8p*wy.asp

--
David Portas
SQL Server MVP
--

#3 (**permalink**) 02-29-2008, 07:27 AM

Sorry. Broken link. Try:
http://msdn.microsoft.com/library/de...qd_02_8pwy.asp

--
David Portas
SQL Server MVP
--

#4 (**permalink**) 02-29-2008, 07:28 AM

Read about three valued logic in any intro SQL book. You got
everything wrong:

(@test = NULL) -> UNKNOWN for all values of @test

This makes a big difference in DDL and DML clauses.

There is no "IS" in SQL -- that reserved word is part of several
multi-word operators such as IS NULL, IS NOT NULL, and some that are
not widely implemented yet.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Basic Syntax explanation