CASE not recognizing nulls on outer joins with no corresponding record

Question

Post reply

CASE not recognizing nulls on outer joins with no corresponding record

railgod

SSC Enthusiast

Points: 154
More actions
April 22, 2009 at 3:54 am

#130208

I have an issue with a case statement not evaluating an empty record as null. I have a select statement thus:
SELECT a.blah, b.de, CASE b.blah WHEN NULL THEN 0 ELSE 1 END as blah,
FROM tableA a
LEFT OUTER JOIN tableB b
ON a.PK = b.FK
In the event that there is no corresponding record in table B the CASE statement evaluates b.blah as NOT NULL. Has anybody encountered this before and if so, did you manage to resolve it and if so, how? 🙂
Thanks

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

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this,

SELECT a.blah, b.de, case when isnull(b.blah,0)> 0 then 1 else 0 end /*CASE b.blah WHEN NULL THEN 0 ELSE 1 END*/ as blah,

FROM tableA a

LEFT OUTER JOIN tableB b

ON a.PK = b.FK

ARUN SAS

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Use a searched case, otherwise you're using a comparison operator which won't work with NULLs:

DROP TABLE #NullSample

CREATE TABLE #NullSample (ID INT, Something VARCHAR(15))

--

INSERT INTO #NullSample (ID, Something)

SELECT 1, 'a value' UNION ALL

SELECT 2, 'another value' UNION ALL

select 3, null

--

SELECT *,

CASE Something WHEN NULL THEN 'NULL' ELSE 'NOT NULL' END AS Blah,

CASE WHEN Something IS NULL THEN 'NULL' ELSE 'NOT NULL' END,

ISNULL(Something, 'Yep it''s null')

FROM #NullSample

Results:

ID Something Blah

----------- --------------- -------- -------- ---------------

1 a value NOT NULL NOT NULL a value

2 another value NOT NULL NOT NULL another value

3 NULL NOT NULL NULL Yep it's null

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

railgod SSC Enthusiast Points: 154 More actions · Answer 3

arun.sas (4/22/2009)
Hi,
try this,
SELECT a.blah, b.de, case when isnull(b.blah,0)> 0 then 1 else 0 end /*CASE b.blah WHEN NULL THEN 0 ELSE 1 END*/ as blah,
FROM tableA a
LEFT OUTER JOIN tableB b
ON a.PK = b.FK
ARUN SAS

This worked a treat, thanks for the help guys!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

No problem - what syntax did you settle for?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden