There might be scenarios when we need to perform a join on either one or the other tables based on a particular condition. These joins are called conditional or dynamic joins.
We take a simple example of an Employee table with a Dept ID column.
Now assume that there is some external data being populated in this table and there is an external Dept table that we need to perform lookup on in case the data is identified as External.
If the data is not External, we perform the lookup on our original Department table.
Understanding the data
In our tables, the flag column - IsExternal identifies the data as External if it is set to value 'X'.
Dept is the original Department table.
DeptExt is the External Department table replicated in our database.
Please use below statements to create the sample tables and insert practice data.
Create table Emp
(EmpID int identity NOT NULL,
DeptID int NULL,
IsExternal char(1) NULL
);
Create table Dept
(DeptID int NOT NULL,
DeptName varchar(100) NULL
);
Create table DeptExt
(DeptID int NOT NULL,
DeptName varchar(100) NULL
);
Insert into Emp VALUES (2,' ');
Insert into Emp VALUES (1,' ');
Insert into Emp VALUES (1,'X');
Insert into Emp VALUES (3,' ');
Insert into Dept VALUES (1,'Sales');
Insert into Dept VALUES (2,'Marketing');
Insert into Dept VALUES (3,'HR');
Insert into DeptExt VALUES (1,'Software');
Insert into DeptExt VALUES (2,'Retail');
Solution 1 -
In the first approach, we perform Left Join on the table Dept for data which is not External.
We also perform a Left Join on DeptExt for data which is marked as External.
In the Select statement, we use COALESCE function to pick the Not Null value for Department Name.
(Since the Left Join queries for which there is no matching data will return NULLs)
SQL Query -
Select EmpID, E.DeptID,
COALESCE(D.DeptName, DE.DeptName)
FROM Emp E
LEFT JOIN Dept D ON E.DeptID = D.DeptID and IsExternal <> 'X'
LEFT JOIN DeptExt DE ON E.DeptID = DE.DeptID AND IsExternal = 'X'
Solution 2 -
In this approach, we perform Left Joins as above as well. We can also perform an INNER JOIN if we are sure all data is populated with valid Dept IDs.
But instead of performing the Left Joins in a single query, we write two separate queries and use a UNION / UNION ALL operator to combine the relevant data.
SQL Query -
Select EmpID, DeptName
FROM Emp LEFT JOIN Dept ON Emp.DeptID = Dept.DeptID
WHERE IsExternal <> 'X'
UNION ALL
Select EmpID, DeptName
FROM Emp LEFT JOIN DeptExt ON Emp.DeptID = DeptExt.DeptID
WHERE IsExternal = 'X'