conditional table joins

Question

Post reply

conditional table joins

Amy.G

SSCrazy

Points: 2305
More actions
June 2, 2011 at 12:53 pm

#252080

I would like to join two tables. If there is no match for one column, I would like to say join with another column. When I use an OR in the ON statement, I get a Cartesian product. In plain English I would like to type in:
SELECT *
FROM T1 INNER JOIN T2
ON T1.Var1=T2.Var1 OR "If there is not match then" T1.Var2=T2.Var1
Does anyone know the code to do this? Is it a CASE statement?
Thank you,
Amy
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mike01 SSChampion Points: 11413 More actions · Answer 1

Have you tried a union? It could be slow based the volume of data, but duplicates would be removed.

SELECT *

FROM T1 INNER JOIN T2

ON T1.Var1=T2.Var1

UNION

SELECT *

FROM T1 INNER JOIN T2

ON T1.Var2=T2.Var1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I would expect a "conditional join" to perform poorly.

My suggestion would be to join to the table twice

SELECT *

FROM T1

LEFT INNER JOIN T2 ON T1.Var1=T2.Var1

LEFT INNER JOIN T2 T2a ON T1.Var2=T2a.Var1

It is then a simple matter to use CASE statements to return either the T2 or T2a variables based on whether or not T2.Var1 is null.

LEFT joins are used so that the query doesn't omit rows that fail either join condition. If both joins fail, you can filter out any rows where both T2.Var1 and T2a.Var1 are null.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Amy.G SSCrazy Points: 2305 More actions · Answer 3

Well, it wasn't the simplest CASE statement ever, but joining the table twice did the trick. thank you.:-D

GSquared SSC Guru Points: 260824 More actions · Answer 4

Actually, Coalesce or IsNull would probably handle the Case statement logic in this scenario.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Gus:

The reason I said use CASE is that you might have cases where the join to T2 succeeds but some other T2.column value is NULL anyway.

In that case, would you want to substitute the value from T2a?

I assume not, but if that assumption is wrong COALESCE or ISNULL would be appropriate.

Amy:

All CASE statements should test against T2.Var1 to see if the join succeeded. Would you post one or two of your actual CASE statements?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

Amy.G (6/2/2011)
I would like to join two tables. If there is no match for one column, I would like to say join with another column. When I use an OR in the ON statement, I get a Cartesian product. In plain English I would like to type in:
SELECT *
FROM T1 INNER JOIN T2
ON T1.Var1=T2.Var1 OR "If there is not match then" T1.Var2=T2.Var1
Does anyone know the code to do this? Is it a CASE statement?
Thank you,
Amy

Can you explain why this is not just a simple OR ?

SELECT *

FROM T1 INNER JOIN T2

ON T1.Var1=T2.Var1

OR T1.Var2=T2.Var1

The only way I can see that not fulfilling your requirement is if you never want T1.Var1 = T2.Var1 AND T1.Var2 = T2.Var2. in which case you just add that condition

SELECT *

FROM T1 INNER JOIN T2

ON (T1.Var1=T2.Var1

OR T1.Var2=T2.Var1)

AND NOT (T1.Var1 = T2.Var1 AND T1.Var2 = T2.Var2)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

GSquared SSC Guru Points: 260824 More actions · Answer 7

The Dixie Flatline (6/2/2011)
Gus:
The reason I said use CASE is that you might have cases where the join to T2 succeeds but some other T2.column value is NULL anyway.
In that case, would you want to substitute the value from T2a?
I assume not, but if that assumption is wrong COALESCE or ISNULL would be appropriate.
Amy:
All CASE statements should test against T2.Var1 to see if the join succeeded. Would you post one or two of your actual CASE statements?

That makes sense. Hadn't thought of it that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Can you explain why this is not just a simple OR ?

Asked and answered.

I would expect a "conditional join" to perform poorly.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

I tested it out. The Double-LEFT join runs over 10 times faster when joining 100,000 rows to 9,000 rows. HOWEVER, the critical difference isn't performance. The Double-LEFT join produced the expected 100,000 rows in the result set. But the conditional joins produced over 180,000 rows in the result set. Apparently the optimizer produces a query plan that returns a row for EACH "OR" or "IN" condition.

create table sourceTable (RowID int identity(1,1) primary key, RowKey_1 int, RowKey_2 int)

--optional

--create nonclustered index IX_Source1 on sourcetable (Rowkey_1)

--create nonclustered index IX_Source2 on sourcetable (Rowkey_2)

create table joinTable (JoinID int identity(1,1) primary key, data varchar (100))

insert into joinTable (data)

select CAST(N as varchar)+' Data blah de blah de blah'

from tally

where N <=9000

declare @X INT = 10000

insert into sourceTable(RowKey_1,RowKey_2)

SELECT TOP 100000

ABS(CHECKSUM(NEWID())) % @X + 1 AS RowKey_1

,ABS(CHECKSUM(NEWID())) % @X + 1 AS RowKey_2

FROM Tally

declare @timer datetime = getdate()

select *

into #temp1

from sourceTable s

join joinTable j on (j.JoinID = s.RowKey_1 or j.JoinID = s.RowKey_2)

select DATEDIFF(ms,@timer,getdate()) as Conditional_Join

set @timer = getdate()

select *

into #temp3

from sourceTable s

join joinTable j on j.JoinID in (s.RowKey_1, s.RowKey_2)

select DATEDIFF(ms,@timer,getdate()) as IN_Join

set @timer = getdate()

select s.*,case when j.JoinID is null then j2.data else j.data end as data

into #temp2

from sourceTable s

left join joinTable j on j.JoinID = s.RowKey_1

left join joinTable j2 on j2.JoinID = s.RowKey_2

select DATEDIFF(ms,@timer,getdate()) as Double_Left_Join

select COUNT(*) as Conditional_Join from #temp1

select COUNT(*) as IN_Join from #temp3

select COUNT(*) as Double_Left_Join from #temp2

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

conditional table joins

Cookies on SQLServerCentral