One More Time, “IS NULL” Is Not The Same That “= NULL”

Today a developer ask me about a issue that he has when executed a query, it must to return rows and he didn’t any get. When I took a look at query I can see a “= NULL” like a filter condition in the where clause. Of course, I see it many times, and it is a error in Oracle, Sybase, MySQL, SQL Server, ……. In this case the person has been working as sql developer since two months ago, it can be a excuse.

I post the, maybe, more simple example;

TEST*ANTO> create table uno (col1 number(5), col2 number(5));

Tabla creada.

TEST*ANTO> insert into uno values (1,2);

1 fila creada.

TEST*ANTO> insert into uno (col1) values (3);

1 fila creada.

TEST*ANTO> select * from uno;

      COL1       COL2
__________ __________
         1          2
         3 ø

REM 
REM AND NOW, THE PROBE
REM 

REM *** This not return anything ***

TEST*ANTO> select * from uno where col2 = NULL;

ninguna fila seleccionada

REM *** This return one row ***

TEST*ANTO> select * from uno where col2 IS NULL;

      COL1       COL2
__________ __________
         3 ø

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s