db2 query condition where column not in () while the column value is null

sql null db2 where-in
Panadol Chong picture Panadol Chong · Jan 7, 2015 · Viewed 30k times · Source

Good day,

I have a row of data with a column, call status, the value inside is null.

If I query select * from table where status not in ('EXP'), before I run this query, I thought I can select the row of data because status=null, consider not in ('EXP') also. After I tried, I found that I cant query out this row of data. Wish to know why this happen.

Try to Google it but maybe my question is not correct so I cant get correct search result from Google.

Kindly advise.

Answer

user4420255 picture user4420255 · Jan 7, 2015

NULL values are only checked with IS NULL or IS NOT NULL

SELECT * 
  FROM table 
 WHERE status NOT IN ('EXP')
    OR status IS NULL;

Hope I have understand your question.

Maybe the other way around

SELECT * 
  FROM table 
 WHERE status NOT IN ('EXP')
   AND status IS NOT NULL;

Related questions

IsNull function in DB2 SQL?

Is there a performant equivalent to the isnull function for DB2? Imagine some of our products are internal, so they don't have names: Select product.id, isnull(product.name, "Internal) From product Might return: 1 Socks 2 Shoes 3 Internal 4 Pants

Read More
Is Null Greater Than Any Date Data Type?

I have this query in DB2 SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A If the SYSDATE is NULL, would it be greater than any value of @A, assuming that @A and SOMESCHEMA.SOMETABLE.SYSDATE is a Date data type? …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More