0

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

Improve this question
4
  • 1
    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? Commented Jul 25, 2013 at 19:08
  • 4
    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 sqlfiddle.com/#!3/3f574/3 Commented Jul 25, 2013 at 19:10
  • Yes. Actually I wan to ignore the join if @JoinWithT2 value is 0 Commented Jul 25, 2013 at 19:11
  • 2
    You really need to define what you mean by 'ignore the join'. Your two snippets don't give the same results. Commented Jul 25, 2013 at 19:19

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

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);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.