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?

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. — Will, Oct 22 '14 at 23:07
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` — Shannon Severance, Oct 22 '14 at 23:19
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) — marksiemers, Oct 23 '14 at 01:50
@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. — simo.3792, Oct 23 '14 at 04:11

simo.3792 · Accepted Answer · 2014-10-23T03:45:37.883

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.

Thanks! This helped me find the right path forward in my brain. :) — Per Lundberg, Jan 07 '23 at 13:25

score 17 · Answer 2 · answered Oct 23 '14 at 11:57

17

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
*/

answered Oct 23 '14 at 11:57

Jithin Shaji

5,893
5
25
47

4

You answered this question quite some ago and just now have seen the value in this answer...especially with integration...thanks again! – J.S. Orris Jan 31 '15 at 02:22

score 3 · Answer 3 · edited Dec 19 '21 at 08:31

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

edited Dec 19 '21 at 08:31

Dale K

25,246
15
42
71

answered Jul 02 '21 at 09:52

Michael Mössler

131
1

What if table columns have different 'collations' – bareMetal Jan 20 '23 at 05:49
UNION [ALL] if the result tables have the same columns. – qwr Jul 09 '23 at 01:17

score 2 · Answer 4 · answered Oct 23 '14 at 04:04

2

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

answered Oct 23 '14 at 04:04

Jason W

13,026
3
31
62

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. – Nick.Mc Oct 23 '14 at 04:15
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). – Jason W Oct 23 '14 at 04:19
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. – Jason W Oct 23 '14 at 04:20
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. – simo.3792 Oct 23 '14 at 04:56
Absolutely right. There are tradeoffs of each dozen way to do it. Both of these are completely valid. – Jason W Oct 23 '14 at 04:59

score 1 · Answer 5 · answered Dec 19 '21 at 07:32

1

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.

answered Dec 19 '21 at 07:32

Ahmed Yousef

11
4

1

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. – Dale K Dec 19 '21 at 08:32

Conditional JOIN Statement SQL Server

5 Answers5

Linked