Conditional JOIN Statement SQL Server

Question

Is it possible to do the following:

IF [a] = 1234 THEN JOIN ON TableA 
ELSE JOIN ON TableB

If so, what is the correct syntax?

Woothen

I believe you would have to use dynamic SQL to obtain your desired result. You could however explain your problem more indepth and maybe there would be a better overall solution. – Catt

Can you please elaborate more with sample data. – Punctate

Short, but correct answer, "No". Can do something like select ... join on tableA on .... where [A] = 1234 union all select ... join on tableA on .... where [A] <> 1234 or [A] is null – Clapper

What is "[a]" - a variable, table? Are you doing a SELECT? What table is in the from clause? Are there foreign keys? You could possibly do this, but more information is needed. You can put different conditionals in your JOIN clauses - to accomplish what I think you want, but a more complete query is needed (with SELECT, FROM, and foreign key refs if applicable) – Marinelli

@bummi It looks like this question is different than the one you marked as possible duplicate, because this is joining to 2 different tables - where the one you linked to is joining to the same table, but using 2 different sets of conditions. – Bough

Answer 1

I think what you are asking for will work by joining the Initial table to both Option_A and Option_B using LEFT JOIN, which will produce something like this:

Initial LEFT JOIN Option_A LEFT JOIN NULL
OR
Initial LEFT JOIN NULL LEFT JOIN Option_B

Example code:

SELECT i.*, COALESCE(a.id, b.id) as Option_Id, COALESCE(a.name, b.name) as Option_Name
FROM Initial_Table i
LEFT JOIN Option_A_Table a ON a.initial_id = i.id AND i.special_value = 1234
LEFT JOIN Option_B_Table b ON b.initial_id = i.id AND i.special_value <> 1234

Once you have done this, you 'ignore' the set of NULLS. The additional trick here is in the SELECT line, where you need to decide what to do with the NULL fields. If the Option_A and Option_B tables are similar, then you can use the COALESCE function to return the first NON NULL value (as per the example).

The other option is that you will simply have to list the Option_A fields and the Option_B fields, and let whatever is using the ResultSet to handle determining which fields to use.

Bough

Thanks! This helped me find the right path forward in my brain. :) – Ply

Answer 2

This is just to add the point that query can be constructed dynamically based on conditions. An example is given below.

DECLARE @a INT = 1235
DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM [sourceTable] S JOIN ' + IIF(@a = 1234,'[TableA] A ON A.col = S.col','[TableB] B ON B.col = S.col') 

EXEC(@sql)
--Query will be
/*
SELECT * FROM [sourceTable] S JOIN [TableB] B ON B.col = S.col
*/

Ornithosis

You answered this question quite some ago and just now have seen the value in this answer...especially with integration...thanks again! – Woothen

Answer 3

You can solve this with union

select a, b
from tablea
join tableb on tablea.a = tableb.a
where b = 1234
union
select a, b
from tablea
join tablec on tablec.a = tableb.a
where b <> 1234

Iou

What if table columns have different 'collations' – Kirtle

UNION [ALL] if the result tables have the same columns. – Worriment

Answer 4

I disagree with the solution suggesting 2 left joins. I think a table-valued function is more appropriate so you don't have all the coalescing and additional joins for each condition you would have.

CREATE FUNCTION f_GetData (
    @Logic VARCHAR(50)
) RETURNS @Results TABLE (
    Content VARCHAR(100)
) AS
BEGIN
    IF @Logic = '1234'
        INSERT @Results
            SELECT Content
            FROM Table_1
    ELSE
        INSERT @Results
            SELECT Content
            FROM Table_2
    RETURN
END
GO

SELECT *
FROM InputTable
    CROSS APPLY f_GetData(InputTable.Logic) T

Andress

I haven't tested it but I'm guessing this one might not perform too well at large scales compared to the outer join method. But I'm willing to learn if you think otherwise. – Yarkand

I would think it depends on the sizes of your 2 tables and if you think you may need to decide between 3 or 4 or more tables down the road. If you think 3+ tables for the decision potentially, I'd definitely avoid the outer join since that gets messy quick. But you're right that the code as written returns the entire Table_1 or Table_2, and because it's TVF, you don't get the benefit of STATISTICS for the query optimizer to leverage in execution plans. To mitigate this, add an additional parameter(s) to the function to only return rows that will be in output (don't plan to filter with where). – Andress

Any by "don't filter with WHERE", I mean avoid filtering columns from the output of the function in the outer query - you want the filtering to occur within the function of table_a or table_b. – Andress

As with most things in SQL there are about a dozen ways to do everything. Some a clearly inefficient once you know all of the behaviour required and the limitations of the implementation. This solution will work probably better IF table_1 and table_2 have the same complete list of fields (in which case the DB probably isn't optimised properly). It won't help if they contain different data. And this probably is overkill if only one or two fields are required from the JOIN tables - as one or two COALESCE are not that hard to implement. – Bough

Absolutely right. There are tradeoffs of each dozen way to do it. Both of these are completely valid. – Andress

Answer 5

I think it will be better to think about your query in a different way and treat them more like sets.

I do believe if you make two separate queries then join them using UNION, It will be much better in performance and more readable.

Adigun

This is really a tip, and therefore suited to a comment, not an answer. A good answer will include the actual solution not just general helpful advice. – Federicofedirko