Systems Engineering and RDBMS

NULL checks

Posted by decipherinfosys on April 12, 2007

Another one of those small mistakes.  A NULL != NULL in SQL language.  One needs to use the IS NULL and/or the IS NOT NULL checks in order to check for NULL values.  And in the SQL Server world, do not change the behavior using ANSI_NULLS setting.  It is un-desirable and leads to confusion.  Here is what I mean by that (SQL Server Syntax):

create table demo_null (col1 int null)
insert into demo_null values (1)
insert into demo_null values (null)
insert into demo_null values (null)
go
set ansi_nulls off
–show 2 records
select * from demo_null where col1 = null
go
set ansi_nulls on
–will show 0 records
select * from demo_null where col1 = null
go

So,  use IS NULL checks always.  Also, remember that whenever the IS NULL or the IS NOT NULL checks are used, if there is an index on that column, the index will not be used since NULL values do not get stored in an index and that is why the optimizer will need to go to the data pages in order to retrieve more information to suffice the requirements of the query.

One Response to “NULL checks”

  1. […] it will result into NULL records after the join and since NULL != NULL in SQL (not considering the ANSI_NULL setting in SQL Server for this post), then the inner join with TableC will get rid of those records. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: