Confusion with NULL values in SQL

Today I was working on a SQL query, and it took me a while before I figured out an issue.

A query goes something like this –  SELECT * FROM Table t1 where t1.UserId NOT IN ( SELECT UserId FROM Table t2 WHERE t2.Status = 1) WHERE Status = 2

Lets assume Status 1 = Active and Status 2 = Terminated and UserId can be NULL. There are few records in Table with UserIds Null for Status = 1. When i ran the query I was expecting few records to output but for some reason there are 0 records. After looking at the query carefully, i noticed that the inner query was returning NULL values and that ends being as below. It return NULL

SELECT * FROM Table t1 where t1.UserId NOT IN ( 1, 2, NULL) — Return 0 records although there are matching records with 1, 2

NULL can be dangerous enough to mess up the whole query and result set. So better handle NULL values properly.

 

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