CODEWITHSUNDEEP
sqlsql-server
Jen K
Jen K

Reputation: 13

How to use isnull in where clause of SQL

I have a bit valued column that has entries 0, 1, NULL.

I need to show all entries where

bcode = 0

Since default 0 is not set for the column hence I need to handle the nulls.

To get all the entries that have null and 0 entry in the bit column I write below query

select code 
from tbl_sample 
where (bcode = 0 or bcode is null)

I'm not sure if this will returns both 0s and NULLs. But if it does to simply the above query will the below query work or will it result to wrong output?

select code 
from tbl_sample 
where isnull(bcode, 0) = 0

or

select code 
from tbl_sample 
where isnull(bcode, '') = 0

What is the right way?

Upvotes: 0

Views: 2595

Answers (1)

Thom A
Thom A

Reputation: 95554

As I mention in the comments, using the syntax you already have is the correct call here. Though you could use ISNULL or COALESCE, that would make the query non-SARGable, which would mean that any indexes you have on the table could not be used to help the RDBMS filter to the rows you want, which could mean the query is significantly slower.

Since default 0 is not set for the column

Honestly, this sounds like that you should be changing your column's definition to bcode bit NOT NULL (I assume bit as it can only be 0 and 1) and then create a DEFAULT CONSTRAINT:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT DF_YourTable_Bcode DEFAULT 0 FOR Bcode;

If you're on the bleeding edge of SQL Server though (2022+) you could use IS DISTINCT FROM (again, assuming your statement that the column can only have 1, 0 and NULL is true and/or bcode is a bit):

SELECT code
FROM dbo.YourTable
WHERE BCode IS DISTINCT FROM 1;

Upvotes: 1

Related Questions