Skip to main content

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:


Create the "employees" table:


Create the "locations" table:

100New YorkUSA

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.


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.