ISNULL in case statement

Question

Post reply

ISNULL in case statement

Johnson330

Old Hand

Points: 345
More actions
January 10, 2023 at 12:26 pm

#4135887
Hi,
I have this SQL fragment
```
SELECT COL1,
CASE WHEN Table1.COL2 = 0 then 'ZERO'
WHEN Table1.COL2 = 1 then 'ONE'
ELSE '' END AS MyResult
FROM Table1
```
Which is the best way to treat Table1.COL2 NULL values as if they were 1 ?
Is this the best way ?
```
WHEN ISNULL(Table.COL2,1) then 'ONE'
```
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 1

COALESCE(TABLE1.COL2,1) ? equals if COL2 is null, treat it as value "1"

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 2

COALESCE vs ISNULL https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

I prefer COALESCE because it is known by other SQL-dialects and it doesn't truncate.

In where-clauses I would use: where X is NULL or X=1

instead of COALESCE (unless you use computed indexed)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Johnson330 wrote:

Hi, I have this SQL fragment
SELECT COL1,
CASE WHEN Table1.COL2 = 0 then 'ZERO'
WHEN Table1.COL2 = 1 then 'ONE'
ELSE '' END AS MyResult
FROM Table1
Which is the best way to treat Table1.COL2 NULL values as if they were 1 ? Is this the best way ?
WHEN ISNULL(Table.COL2,1) then 'ONE'
Thank you

We will need your help in order to be able to help you, so please help us!

😎

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

ISNULL/COALESCE is not as clear as explicitly checking for NULL:

SELECT COL1,
CASE WHEN Table1.COL2 = 0 then 'ZERO'
WHEN Table1.COL2 = 1 OR Table1.Col2 IS NULL then 'ONE'
ELSE '' END AS MyResult
FROM Table1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

ScottPletcher wrote:

ISNULL/COALESCE is not as clear as explicitly checking for NULL:
SELECT COL1,
CASE WHEN Table1.COL2 = 0 then 'ZERO'
WHEN Table1.COL2 = 1 OR Table1.Col2 IS NULL then 'ONE'
ELSE '' END AS MyResult
FROM Table1

That and the fact that neither column might be NULL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)