Conditional Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Join

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-08 : 20:04:24

I'd like to join a table, call it Table1, to one of 5 other tables, depending on whether a condition is met. I don't want to have to execute LEFT JOIN on a chain of all five and then filter the results.

This isn't accurate syntax, but something along the lines of:


CREATE PROC spCond (
@SomeVar VARCHAR(10) = NULL
)
AS
BEGIN

INSERT #temp (ID, SomeVar)

SELECT DISTINCT ID, @SomeVar
FROM Table1

IF @SomeVar = 'A'
LEFT JOIN (
Table2 ON Table2.[Column] = Table1.[Column]
WHERE Val = @SomeVar 
)
ELSE @SomeVar = 'B'
LEFT JOIN (
Table3 ON Table3.[Column] = Table1.[Column]
WHERE Val = @SomeVar 
)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 20:57:43

[code]from
table1
left join table2 on @somevar = 'a' and table2.column = table1.column
left join table3 on @somevar = 'b' and table3.column = table1.column
[/code]

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-09 : 12:08:53

Thanks; assuming the join expression is evaluated in that order, I like it!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 13:00:20

You can check the query plan. Tables that have no business being there should not show up anywhere in the plan. They should get eliminated during one of the earlier phases of query plan generation.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-09 : 17:03:33

Thanks; all of them show up in the actual execution plan, with hash matches. The selected table's parallelism is different (repartition rather than distribute for the other tables). Nonetheless, the result set returns the right substitution for the variable chosen, and not from all tables, so that part looks good.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 18:30:15

You are right, I tested it out and my intuition was wrong. I would have expected at most a constant scan. But the query spends time and efforts on the table that would fetch no rows at all. So if efficiency is a consideration this may not be the way to do it.

Hopefully someone else will suggest a better alternative.

If I replace @somevar = 'a' with 'b' = 'a', then it behaves as I expect. But when it is comparing against a variable, the optimizer assumes that the logical expression could evaluate to true.

So the query optimizer is not as smart as I thought!

On second thoughts, as has been proven time and again, I would bet that it is not the query optimizer that is not smart, it is someone else and I see only one someone else around, and that would be yours truly.

SNM.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-09 : 20:02:51

Kind to say, thanks again for your input. I'd like my query to be as efficient as possible, but as it is, the runtime is not very long, under a minute. Right now I'm on an 'efficiency quest' so I hope I can keep finding the gold!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 00:01:23

better use the IF condition e.g.

If @somevar = 'a'

Select Columns
From table1
left join table2 on table2.column = table1.column
Where Val= @SomeVar

If @somevar = 'B'

Select Columns
From table1
left join table3 on table3.column = table1.column
Where Val= @SomeVar

Cheers
MIK

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-10 : 12:17:18

quote:
Originally posted by MIK_2008

better use the IF condition e.g.

If @somevar = 'a'

Select Columns
From table1
left join table2 on table2.column = table1.column
Where Val= @SomeVar

If @somevar = 'B'

Select Columns
From table1
left join table3 on table3.column = table1.column
Where Val= @SomeVar

Cheers
MIK

My query is now too long to be this compact. 44 LOC for the SELECT clause alone, due to a CASE expression for each LEFT JOIN option.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-10 : 13:20:34

I should say, a CASE expression for all LEFT JOINs, WHEN/THEN for each, not five separate CASE expressions in SELECT for each LEFT JOIN.

As it is, it seems to work best with


FROM Table1 AS tbl1
LEFT JOIN DB.dbo.Table2 AS tbl2 ON tbl2.Column = tbl1.Column --T
   AND @SomeVar = 'a' --if F, then the whole LEFT JOIN should evaluate to F
LEFT JOIN DB.dbo.Table3 AS tbl3 ON tbl3.Column = tbl1.Column --T
   AND @SomeVar = 'b' --if F, then the whole LEFT JOIN should evaluate to F

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-10 : 13:36:11

Two questions for you, more out of curiosity than any possibility of me contributing anything to solve your problem:

1. Do all the tables still show up in the query plan?

2. If you modified the code to remove every other join except the one that would evaluate to true and ran the query, is performance substantially better?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-10 : 14:34:56

The most effecient way is probably going to be dynamic sql. And, from what you have mentioned, it might be more maintainable than using IF-ELSE conditional execution.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-03-11 : 14:07:57

All tables still show up in the query plan, yes. I was hoping they would be eliminated. I have not yet removed every other join, to test performance. Hopefully today.

Yes, IF-THEN blocks may be more maintainable, easier to read. Will try that also, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources