Search code examples
sqlnulldb2where-in

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

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.

Solution

  • 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;