Joining the Table Conditionally in SQL

Conditional JOIN Statement SQL Server

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.

Conditional JOIN different tables

You could use an outer join:

select *
  from USER u
  left outer join EMPLOYEE e ON u.user_id = e.user_id
  left outer join STUDENT s ON u.user_id = s.user_id
 where s.user_id is not null or e.user_id is not null

alternatively (if you're not interested in the data from the EMPLOYEE or STUDENT table)

select *
  from USER u
 where exists (select 1 from EMPLOYEE e where e.user_id = u.user_id)
    or exists (select 1 from STUDENT s  where s.user_id = u.user_id)

conditional join in sql

There are several ways to solve this

IF()
IFNULL(),
COALESCE(),
CASE

Using IF

SELECT * FROM table1 JOIN table2 ON 
IF(table1.id1 IS NOT NULL,table1.id1, table1.id2)=table2.id;

Using IFNULL

SELECT * FROM table1 JOIN table2 ON 
IFNULL(table1.id1, table1.id2)=table2.id;

Using COALESCE

SELECT * FROM table1 JOIN table2 ON 
COALESCE(table1.id1, table1.id2)=table2.id;

Using Case

SELECT * FROM table1 JOIN table2 ON 
(CASE 
WHEN table1.id1 IS NULL THEN table1.id2
ELSE table1.id1 
END) = table2.id;

Refer http://sqlfiddle.com/#!9/8076eb/4

SQL Server conditional join techniques

Your approach 3 can work fine. You should definitely use UNION ALL not UNION though so SQL Server does not add operations to remove duplicates from the tables.

For best chances of success with approach 3 you would need to add an OPTION (RECOMPILE) hint so that SQL Server simplifies out the unneeded table reference at compile time at the expense of recompiling it on each execution.

If the query is executed too frequently to make that approach attractive then you may get an OK plan without it and filters with startup predicates to only access the relevant table at run time - but you may have problems with cardinality estimates with this more generic approach and it might limit the optimisations available and give you a worse plan than option 2.

Conditional join in SQL Server dependent on other table values

Just add your conditions to the joins, and then use a case statement to pull the correct field to your result set e.g.

select A.D, A.E, A.F
  , case when B.[Name] is not null then B.[Name] else C.[Name] end [Name]
from TableA A
left outer join TableB B ON A.B = B.ID and A.E = 1
left outer join TableC C ON A.B = C.ID and A.E = 2

Conditional Join in SQL Server stored procedure

The left join is fine. But you can also use exists:

Select u.*
from users u
where (u.filter1 = 1 and
       exists (select 1 from filtertable1 ft where u.filtercount1 = ft.filtercount1)
      ) or
      (u.filter2 = 1 and
       exists (select 1 from filtertable2 ft where u.filtercount2 = ft.filtercount1)
      ) or
      (u.filter3 = 1 and
       exists (select 1 from filtertable3 ft where u.filtercount3 = ft.filtercount1)
      ) ;

Note: I'm not sure if you want and or or between the conditions. With and the logic would be slightly different.

---

Related Topics