altenative to coalesce function on Joining 2 tables with = condition

Database
Enthusiast

altenative to coalesce function on Joining 2 tables with = condition

Column is one table

column 1 is another table

column 2 is different table 

 

Column=coalesce(column1,column2)

If Column1 is null then passing column2 But using coalesce in view will degrade performace little bit.is there any way to handle without coalesce function.

Logic Column is equal to one of not null column in column1 & column 2.

 

5 REPLIES
Senior Apprentice

Re: altenative to coalesce function on Joining 2 tables with = condition

Hi,

 

Out of curiosity, what makes you say "But using coalesce in view will degrade performance little bit"? If the functionality that you need to implement means that you need to join two tables then I doubt if using COALESCE will be noticeable in terms of performance - either elapsed time, cpu , I/O etc.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Junior Contributor

Re: altenative to coalesce function on Joining 2 tables with = condition

In worst case the COALESCE will result in a *product join*, can you show your current query?

 

Is only one of those columns (or both) not null?

 

Alternative might be two Left Joins...

Enthusiast

Re: altenative to coalesce function on Joining 2 tables with = condition

i can't paste the original code: renamed column as ID1 & ID2

FULL OUTER JOIN DLAB.Table1
ON Table1.ID=coalesce(table2.ID, table2.ID)  - any one can be notnull
ANDTable1.ID1=coalesce(table2.ID1, table2.ID1)

Junior Contributor

Re: altenative to coalesce function on Joining 2 tables with = condition

Could you please write the correct join condition? Currently it's the same as

ON Table1.ID=table2.ID 
AND Table1.ID1=table2.ID1

Is that the only join?

 

Enthusiast

Re: altenative to coalesce function on Joining 2 tables with = condition