COALESCE in Left Join

Question

Post reply

COALESCE in Left Join

Naidu PK

SSCrazy

Points: 2481
More actions
April 28, 2015 at 8:55 pm

#317040

Comments posted to this topic are about the item COALESCE in Left Join
Thanks,
Naveen.
Every thought is a cause and every condition an effect

Viewing 15 posts - 1 through 15 (of 19 total)

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

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22680

April 29, 2015 at 12:55 am

#1794190

Good brain training!

Thanks!

😀

Toreador SSChampion Points: 11368 More actions · Answer 2

Toreador

SSChampion

Points: 11368

April 29, 2015 at 1:07 am

#1794192

Only 54% correct? I was expecting close to 100%!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Great question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 4

Toreador (4/29/2015)
Only 54% correct? I was expecting close to 100%!

It depends if one has enough time to read correctly the question of th day.

jburet SSC Journeyman Points: 81 More actions · Answer 5

The question assumes SET ANSI_NULLS ON Although it is ON by default, and will stay ON on later SQL version it's not always the case on older SQL Server versions.

djj SSCoach Points: 18831 More actions · Answer 6

Well I tried it and got 7 and 7.

create table #abc (a int, b int, c int);

INSERT INTO #abc values

(NULL,NULL,3),

(1,NULL,3),

(1,NULL,NULL),

(NULL,2,NULL);

Create table #xyz (x int, y int, z int);

INSERT INTO #abc values

(NULL,2,3),

(NULL,NULL,1),

(NULL,NULL,2);

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

-- Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON COALESCE(a.a, 9) = COALESCE(x.x, 9)

AND COALESCE(a.b, 9) = COALESCE(x.y, 9);

drop table #abc;

drop table #xyz;

julian.fletcher SSCrazy Points: 2965 More actions · Answer 7

Probably because you've got a typo:

Create table #xyz (x int, y int, z int);

INSERT INTO #abc values

(NULL,2,3),

(NULL,NULL,1),

(NULL,NULL,2);

djj SSCoach Points: 18831 More actions · Answer 8

Thanks! Copy paste will get you in trouble.:-D

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Good question to start the day. An exercise in clear thinking is always good for the brain. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

Thanks. Good training on COALESCE which I rarely use.

chgn01 SSCarpal Tunnel Points: 4365 More actions · Answer 11

How about use ISNULL?

-- Q3

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON ISNULL(a.a, 9) = ISNULL(x.x, 9)

AND ISNULL(a.b, 9) = ISNULL(x.y, 9);

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

chgn01 (4/29/2015)
How about use ISNULL?
-- Q3
SELECT
*
FROM
#abc a
LEFT JOIN #xyz x
ON ISNULL(a.a, 9) = ISNULL(x.x, 9)
AND ISNULL(a.b, 9) = ISNULL(x.y, 9);

In this case, they'll act the same way. But that won't be always the case.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Revenant SSC-Forever Points: 42467 More actions · Answer 13

Revenant

SSC-Forever

Points: 42467

April 29, 2015 at 9:26 am

#1794347

Thanks for this interesting question!

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 14

Luis Cazares (4/29/2015)
chgn01 (4/29/2015)
How about use ISNULL?
-- Q3
SELECT
*
FROM
#abc a
LEFT JOIN #xyz x
ON ISNULL(a.a, 9) = ISNULL(x.x, 9)
AND ISNULL(a.b, 9) = ISNULL(x.y, 9);
In this case, they'll act the same way. But that won't be always the case.

If there's no coercing data going on, it should be the same. There's probably some esoteric exception though.

Don Simpson

I'm not sure about Heisenberg.