SQL Server: RIGHT JOIN on Multiple Columns to Spot Missing Data

In a RIGHT JOIN operation, you can combine multiple columns from two tables based on a common key. The result will include all rows from the right table and the matched rows from the left table. If no match is found in the left table, the corresponding columns will contain NULL values.

Here's the general syntax to perform a RIGHT JOIN with multiple columns:

Syntax to perform a right join with multiple columns

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.column1 = right_table.column1
AND left_table.column2 = right_table.column2

Sample Data to Demo Right Join Multiple Columns

You can use this DB Fiddle to follow along and practice right joining multiple columns

Let's create three tables, "departments", "employees", and "locations". We can then perform a left join on two columns, "dept_id" and "location_id," to identify departments without any employees.

Create the "departments" table:

dept_id	location_id	department_name
1	100	HR
2	200	Finance
3	100	IT
4	300	Marketing

Create the "employees" table:

employee_id	first_name	last_name	dept_id	location_id
101	John	Doe	1	100
102	Jane	Smith	2	200
103	Michael	Johnson	1	100
104	Emily	Williams	3	100

Create the "locations" table:

location_id	city	country
100	New York	USA
200	London	UK
300	Paris	France

Right Join Multiple Columns: dept_id and location_id

To identify departments without any employees, we can perform a right join on multiple columns (dept_id and location_id) between the "departments" and "employees" tables.

Right join multiple columns to identify departments without any employees

SELECT d.dept_id, d.department_name
FROM employees e
RIGHT JOIN departments d
ON d.dept_id = e.dept_id AND d.location_id = e.location_id
WHERE e.employee_id IS NULL;

We get a query result indicating that Marketing department in our sample data set does not have any employees.

dept_id	department_name
4	Marketing

The above SQL query is selecting information from two tables, "employees" and "departments," using a right join. The goal of the query is to find departments that have no employees associated with them.

Here's a step-by-step explanation of the query:

1. The query starts by selecting two specific columns, "dept_id" and "department_name," from the "departments" table. These columns will be included in the final result.

2. The query then performs a right join between the "employees" table (aliased as 'e') and the "departments" table (aliased as 'd'). A right join means that all rows from the "departments" table will be included in the result, regardless of whether there are matching rows in the "employees" table.

3. The join condition is specified in the ON clause: It says to match rows based on the "dept_id" and "location_id" columns in both tables. This ensures that the departments and employees are joined based on their respective "dept_id" and "location_id" values.

4. The WHERE clause is used to filter the result further. It includes the condition "e.employee_id IS NULL." This condition ensures that only those rows are included in the result where there is no matching employee record in the "employees" table. In other words, it selects departments that have no employees associated with them.

In summary, the query returns a list of departments (along with their IDs and names) that don't have any employees working in them. If there is no corresponding employee record for a department, the employee-related columns will contain NULL values in the result.