user1030181 user1030181 - 5 months ago 14
SQL Question

how to filter records that including null values and other status

Currently i am running below query in sql server. How to filter whose checkpointstatus is not 'Completed' including null values. But the issue is i am not able to get those records whose values are null

SELECT * FROM TAMS.OrderDetail WHERE CheckPointStatus != 'Completed'


I have tried to fix this by

SELECT * FROM TAMS.OrderDetail WHERE coalesce(CheckPointStatus, '') != 'Completed'


But just want to make sure is this correct way

Answer

Your solution:

SELECT * FROM TAMS.OrderDetail WHERE coalesce(CheckPointStatus, '') !=  'Completed'

works fine. Another solution that is more explicit, simple, and possibly cheaper would be

SELECT * FROM TAMS.OrderDetail 
    WHERE CheckPointStatus != 'Completed' OR CheckPointStatus IS NULL

which will match all records not equal to Completed or where the value is null.

Comments