Conditional JOIN Statement SQL Server

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 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.

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

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

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

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.