Conditional Joins in Sql Server based on variable

Question

I want to take a join if and only if some condition is true. Ex-

Table Employee
emp_no
emp_name
dept_no

Table Departnemt
dept_no
dept_name

declare @takeJoin BIT

select * from Employee e
if @takeJoin then
inner join Department d on e.dept_no=d.dept_no

if @takeJoin is 1 then only inner join should be taken otherwise all records from Employee should be returned as it is.

Can we do it with writing general if else statement? This is just a sample, actual query is huge so general

If @takeJoin =1
begin
end
else
begin

end

will not be an appropriate solution.

Is something like this possible?

Duplicate of http://stackoverflow.com/questions/17828117/sql-do-inner-join-if-condition-met — Mike, Dec 01 '14 at 14:25

score 4 · Answer 1 · answered Dec 01 '14 at 14:57

Use a Left Join to always get Employee records and filter them out in the WHERE clause. We use DISTINCT in SELECT to prevent potential one-to-many relationship between Employee and Department when @TakeJoin = 0 (even though this is probably one-to-zero/one). Just wanted to show the technique for the more complex scenario of one-to-many.

SELECT DISTINCT e.emp_no,
   e.emp_name,
   e.dept_no,
   CASE WHEN @TakeJoin = 1 THEN d.dept_no ELSE NULL END AS dept_dept_no
   CASE WHEN @TakeJoin = 1 THEN d.dept_name ELSE NULL END AS dept_name
FROM Employee e
   LEFT OUTER JOIN Department d ON e.dept_no = d.dept_no
--If @TakeJoin = 1/True, then make sure d.dept_no is not null to filter out Employee
--who have no department.
WHERE
--Mimic INNER JOIN on @TakeJoin = 1
((@TakeJoin = 1 AND d.dept_no IS NOT NULL) OR
--"Ignore" the Join and take all records
@TakeJoin = 0)

Gordon Linoff · Answer 2 · 2014-12-01T15:36:29.083

The simplest way is to use an if:

if @TakeJOin = 'Yes'
    select *
    from Employee e inner join
         Department d
         on e.dept_no = d.dept_no;
else
    select *
    from Employee e;
end;

With conditional logic in the SQL, this should work:

You can do this:

select e.*
from Employee e left join
     Department d
     on e.dept_no = d.dept_no and @TakeJoin = 'Yes'
where (@TakeJoin = 'Yes' and d.dept_no is not null) or
      (@TakeJoin <> 'Yes')

Note that you cannot change the number of columns being returned. If you do:

select *
from Employee e left join
     Department d
     on e.dept_no = d.dept_no and @TakeJoin = 'Yes'
where (@TakeJoin = 'Yes' and d.dept_no is not null) or
      (@TakeJoin <> 'Yes')

Then you will get all the columns for Department, but they will have NULL values when @TakeJoin is false (however you represent that).

I think you would get a Cartesian product between Employee and Department if @TakeJoin = 'No' since that would hold for every row in both tables. — Patrick, Dec 01 '14 at 14:31
@Patrick . . . You are very right. I think my brain was asleep. — Gordon Linoff, Dec 01 '14 at 15:36

score 1 · Answer 3 · answered Jan 11 '20 at 17:33

Yes, you can, but you should use option recompile:

DECLARE @doJoin BIT= 0

SELECT *
FROM A
LEFT JOIN B on A.x = B.y and @doJoin = 1
 option (recompile)

Without option recompile, SQL Server will be create (and cache) a more generic execution plan, which always include left join, even if it's not needed in your specific case. With this option you will see a clustered index scan for table A in the execution plan, without any access to "B".

Conditional Joins in Sql Server based on variable

3 Answers3