How to use isnull in where clause of SQL

Question

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?

You can do that, but it's not performant. Stick to bcode=0 OR bcode IS NULL. — Thom A
– Thom A ♦, Commented Nov 11, 2022 at 9:02
Generally, it's recommended to do not use non-standard functions when not required. I agree your query with OR should be prefered, but even if you want to avoid that, better use COALESCE than ISNULL because COALESCE is a standard function. — Jonas Metzler
– Jonas Metzler, Commented Nov 11, 2022 at 9:07
Don't change the query. Trying to filter on function results prevents the database from using indexes. The query engine will have to scan the entire table to calculate the function result before it filters the rows. That means the engine will have to scan all 1M rows even if only just 100 have NULL or 0 in bcode — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 11, 2022 at 9:08
@JonasMetzler this has nothing to do with non-standard functions - no database is compliant with the SQL standard beyond the most basic level. COALESCE is as bad as ISNULL and forces a full table scan — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 11, 2022 at 9:09
If you would read my comment more precise, you would notice I agree that both COALESCE and ISNULL should not be used in this case. — Jonas Metzler
– Jonas Metzler, Commented Nov 11, 2022 at 9:10

Thom A · Accepted Answer · 2022-11-11 10:04:49Z

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;

So setting a default value for the column is the best way to avoid null handling.
I didn't say that, @JenK . I said that in your scenario it seems that you shouldn't be allowed NULL values, and likely should also be using a DEFAULT value.

Collectives™ on Stack Overflow

How to use isnull in where clause of SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related