RSS

Oracle IN clause and NULL

07 May

Suppose in Oracle DB there is a table called MyTable which has a column called MyColumn. The value in MyColumn could be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’ and NULL. I want to get the records where the value of MyColumn is not ‘A’ or ‘B’. Here is an intuitive query SQL:

SELECT t.* FROM MyTable WHERE MyColumn NOT IN ('A', 'B')

I thought it would return records with values ‘C’, ‘D’, ‘E’ and NULL in MyColumn. But it doesn’t return the results as I expected. I won’t be able to get the results where the value of MyColumn is NULL since a NULL can not be equated with anything. The query needs to be modified like this:

SELECT t.* FROM MyTable WHERE MyColumn NOT IN ('A', 'B') OR MyColumn IS NULL
Advertisements
 
Leave a comment

Posted by on May 7, 2012 in Oracle, SQL

 

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

 
%d bloggers like this: