Conditional join

Question

I am trying to achieve conditional join like this

declare @JoinWithT2 bit = 1;

select T1.* from T1 
inner join T2 on (@JoinWithT2 = 0 or T1.Id=T2.Id)

This works fine, when I pass @JoinWithT2 value 1 it gives me result using the join of both the tables, and when I pass @JoinWithT2 value 0 it returns all the results from T1 ignoring the join.

This is all working good but I am concerned about the performance because the above can also be achieved like this

if @JoinWithT2=0
begin
    select T1.* from T1 
end
else
begin
    select T1.* from T1 
    inner join T2 on (T1.Id=T2.Id)
end

What is the best way to achieve conditional join, first one or the second?

I personally feel second one should be better for performance as it does not involve T2 at all while the first query may be using T2

It doesn't ignore the join. It always joins the table. If JoinWithT2 = 1 then it does a correct join (on ID), and if it is 0, you join all records, effectively turning your join into a cross join. Is that what you want? — GolezTrol, Jul 25 '13 at 19:08
Note that where `@JoinWithT2` is 0, the first query is **not** equivalent to `select T1.* from T1` - instead, it becomes an implicit cartesian join - see http://sqlfiddle.com/#!3/3f574/3 — , Jul 25 '13 at 19:10
Yes. Actually I wan to ignore the join if `@JoinWithT2` value is 0 — Pawan Nogariya, Jul 25 '13 at 19:11
You really need to define what you mean by 'ignore the join'. Your two snippets don't give the same results. — GolezTrol, Jul 25 '13 at 19:19

score 2 · Answer 1 · answered Jul 25 '13 at 19:10

I'm confused. The following should do either a Cartesian product or inner join, depending on the value of JoinWithT2:

declare @JoinWithT2 bit = 1;

select T1.*
from T1 inner join
     T2
     on (@JoinWithT2 = 0 or T1.Id=T2.Id);

When @JoinWithT2 is 0 then the on clause is always true. That makes the join equivalent to a cross join.

The following is conditional logic for doing a join or taking the first table:

declare @JoinWithT2 bit = 1;

select T1.*
from T1 left outer join
     T2
     on (@JoinWithT2 = 1 and T1.Id=T2.Id)
where @JoinWithT2 = 0 or T2.Id is not null;

From a performance perspective, the separate statement in the if are generally going to perform better. There is more information for the SQL engine to optimize on. However, if you have an index on T2(id), then the performance difference may not be significant, relative to other parts of the query, such as returning the results back to the user.

score 1 · Answer 2 · answered Jul 25 '13 at 19:15

This is a case where the best choice is likely to be with dynamic SQL althouth the or conditions are not as bad in newr version of SQL server as the older ones. The if statement is nice but gets hard to maintain after you add two or three other tables that may or may not be joined depending on particular conditions. The or condition is likely to be slow as time goes on and more things are added to the query. Building a search type query with just the things you need for this particular set of parameters is often the best choice.

You might want to read:

http://www.sommarskog.se/dyn-search-2008.html

or (if youa re using SQL SERVER 2005 or earlier) http://www.sommarskog.se/dyn-search-2005.html

score 1 · Answer 3 · answered Jul 25 '13 at 19:19

1

Why do you need a join if you don't select anything from T2? The same could be achieved by something like:

select T1.*
from T1 
where @JoinWithT2 = 0 or T1.Id in (select T2.Id from T2);

answered Jul 25 '13 at 19:19

mustaccio

18,234
16
48
57

Conditional join

3 Answers3