Question about "coalesce"

Question

Post reply

Question about "coalesce"

ichiyo85

SSC Veteran

Points: 286
More actions
April 11, 2011 at 7:34 am

#248694

Hi experts,
I have 2 tables where I want to join by ID1, IF NOT THEN join by ID2.
Would this code work?
select * from table1
left join table2
on coalesce(table1.ID1,table1.ID2) = coalesce(table2.ID1,table2.ID2)
I feel like I am missing something with this code, but I can't catch where. Please advise. Thank you very much for your input!

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

is it possible that Table1.ID1= Table2.ID2?, or that Table1.ID2= Table2.ID1?

wouldn't it be more correct to join with an OR?

select * from table1

left join table2

on (table1.ID1 = table2.ID1)

OR (table1.ID2 = table2.ID2)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ichiyo85 SSC Veteran Points: 286 More actions · Answer 2

Hi Lowell,

It is possible to have ID1=ID2. Would I be sure to know I am joining by ID1 fist, and if not ID2 with your code?

Thank you so much for your help!

Lowell SSC Guru Points: 323482 More actions · Answer 3

ichiyo85 (4/11/2011)
Hi Lowell,
It is possible to have ID1=ID2. Would I be sure to know I am joining by ID1 fist, and if not ID2 with your code?
Thank you so much for your help!

well, you know your data better than i do;

I'd like to see some sample data, but so far, based on your questions....

the COALESCE returns the first non-null object in the parameter array. so you have [Table1] where either ID1 and ID2 can be null, but not both? is that right? and the same for [Table2]?;

if that's true, then your coalesce method sounds fine;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 4

I would reccomend using ISNULL() instead of COALESCE()

SQL Server will most often interpret COALESCE as a CASE expression which can hinder the query optimizer from generating/choosing an optimal query plan while ISNULL is a direct comparison....

While both will work just fine for the most part for smaller datasets, however, running this against several million rows of data will result in a slower running query if you use the COALESCE.

Just a thought!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ichiyo85 SSC Veteran Points: 286 More actions · Answer 5

Thank you very much experts.

Yes I will not have both ID1 and ID2 to be null. Also, ISNULL might not be a good idea for me because ID1 is set as "Unassigned" instead of "NULL" in my database.

I think I will stick to coalesce for now. Again, I really appreciate your help!!!

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 6

ISNULL may still be preferred as ISNULL will cast the result to the datatype of the first expression evaluated.

Again, just a performance thought - hope it works out either way!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 7

Please refer the following link:

http://sqlzealot.blogspot.com/search/label/System%20Functions

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

ichiyo85 (4/11/2011)
Hi experts,
I have 2 tables where I want to join by ID1, IF NOT THEN join by ID2.
Would this code work?
select * from table1
left join table2
on coalesce(table1.ID1,table1.ID2) = coalesce(table2.ID1,table2.ID2)
I feel like I am missing something with this code, but I can't catch where. Please advise. Thank you very much for your input!

Does the above query return the expected results? If it does, then you can almost certainly improve the performance by using OR:

SELECT *

FROM table1 t1

LEFT JOIN table2 t2

ON t2.ID1 = t1.ID1

OR t2.ID1 = t1.ID2

OR t2.ID2 = t1.ID1

OR t2.ID2 = t1.ID2

WHERE t1.ID1 IS NOT NULL AND t2.ID1 IS NOT NULL

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

ichiyo85 (4/11/2011)
Thank you very much experts.
Yes I will not have both ID1 and ID2 to be null. Also, ISNULL might not be a good idea for me because ID1 is set as "Unassigned" instead of "NULL" in my database.
I think I will stick to coalesce for now. Again, I really appreciate your help!!!

"Unassigned"? Can you elaborate on this?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ichiyo85 SSC Veteran Points: 286 More actions · Answer 10

"Unassigned" is just a name (nvarchar) that I gave to those cells that don't have values because I didn't want to leave them as NULL. Would it create any problem?

Thank you so much for all your comments!

ichiyo85 SSC Veteran Points: 286 More actions · Answer 11

Also,

SELECT *

FROM table1 t1

LEFT JOIN table2 t2

ON t2.ID1 = t1.ID1

OR t2.ID1 = t1.ID2

OR t2.ID2 = t1.ID1

OR t2.ID2 = t1.ID2

WHERE t1.ID1 IS NOT NULL AND t2.ID1 IS NOT NULL

would not return what I need because I don't want to have t2.ID1 = t1.ID2 or t2.ID2 = t1.ID1 .

I want to join FIRST by t2.ID1 = t1.ID1 and if ID1 does not exist join using t2.ID2 = t1.ID2.

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 12

ichiyo85 (4/12/2011)
"Unassigned" is just a name (nvarchar) that I gave to those cells that don't have values because I didn't want to leave them as NULL. Would it create any problem?
Thank you so much for all your comments!

It won't create a problem per se, but you can't really use COALESCE or ISNULL if there are not any NULL values can you?

mthurber Mr or Mrs. 500 Points: 523 More actions · Answer 13

Let me get this straight - if ID1 is not 'Unassigned' then you want to use it for the join, but if ID1 is 'Unassigned,' then you want to use ID2, right?

In that case, how about this:

SELECT *

FROM table1 t1

LEFT JOIN table2 t2

ON (t2.ID1 = t1.ID1 AND t1.ID1 <> 'Unassigned')

OR (t2.ID2 = t1.ID2 AND t1.ID1 = 'Unassigned')

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 14

ichiyo85 (4/12/2011)
"Unassigned" is just a name (nvarchar) that I gave to those cells that don't have values because I didn't want to leave them as NULL. Would it create any problem?
Thank you so much for all your comments!

What do you do with your integer columns? 😛

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]