Determine table to join based on the condition

Question

I am not sure if I've missed to search properly, but I couldn't get the exact question as mine

This is something similar but not exact

https://stackoverflow.com/questions/11533636/determining-which-table-to-join-to

Actually I want to decide with which table to join based on the parameter passed to stored procedure, case when didn't work

Kind of

select * from Table1 
       left join (case when @Parameter<>NULL Then Table2 else Table3 end) final
       on Table1.Col1 = final.Col1

Table2 and Table3 has same structure

Answer 1

I can think of a few of options:

1: IF ... ELSE

IF @Parameter IS NULL

  SELECT *
  FROM T1
    INNER JOIN T2 ON T1.ID = T2.ID

ELSE

  SELECT *
  FROM T1
    INNER JOIN T3 ON T1.ID = T3.ID

2: Dynamic T-SQL

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'SELECT *
  FROM T1
    INNER JOIN ' + CASE WHEN @Parameter IS NULL THEN N'T2 t' ELSE N'T3 t' END
  + N' ON T1.ID = t.ID';

EXEC sp_executesql @SQL;

3: UNION ALL and subquery.

  SELECT *
  FROM T1
    INNER JOIN
    (
      SELECT *
      FROM T2
      WHERE @Parameter IS NULL
      UNION ALL
      SELECT *
      FROM T3
      WHERE @Parameter IS NOT NULL
    ) t ON T1.ID = t.ID

For this last one you'd have to check the plan the optimiser creates to see if it's OK performance wise.

It seems like you're looking for code reuse, so maybe option 2 is your best one. T-SQL doesn't really lend itself to this sort of polymorphism but you can use workarounds in some cases.

Taking a step back, one question to ask is, if the tables have the same structure, maybe you should just use one table? Then you could use @Parameter to just filter the rows you require instead of trying to create dynamic queries.

It's also worth noting that in situations like this you could handle the code-generation at the application layer with ORMs and the like.