SQL Query - how do filter by null or not null

Question

I want to filter a record....

If statusid is null, filter the record (where statusId is not null)

If statusid is not null, filter the record where statusid is equal to the specified statusid.

How do I do this?

Answer 1

Just like you said

select * from tbl where statusid is null

or

select * from tbl where statusid is not null

If your statusid is not null, then it will be selected just fine when you have an actual value, no need for any "if" logic if that is what you were thinking

select * from tbl where statusid = 123 -- the record(s) returned will not have null statusid

if you want to select where it is null or a value, try

select * from tbl where statusid = 123 or statusid is null

Answer 2

How about statusid = statusid. Null is never equal to null.

Answer 3

WHERE something IS NULL

and

WHERE something IS NOT NULL

Answer 4

set ansi_nulls off go select * from table t inner join otherTable o on t.statusid = o.statusid go set ansi_nulls on go

Answer 5

Wherever you are trying to check NULL value of a column, you should use

IS NULL and IS NOT NULL

You should not use =NULL or ==NULL

Example(NULL)

select * from user_registration where registered_time IS NULL 

will return the rows with registered_time value is NULL

Example(NOT NULL)

select * from user_registration where registered_time IS NOT NULL 

will return the rows with registered_time value is NOT NULL

Note: The keyword null, not null and is are not case sensitive.

Answer 6

I think this could work:

select * from tbl where statusid = isnull(@statusid,statusid)