Skip to main content

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

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

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

Syntax to perform a left join with multiple columns
SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.column1 = right_table.column1
AND left_table.column2 = right_table.column2

Sample Data to Demo Left 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

Left Join Multiple Columns: dept_id and location_id

Left join multiple columns: dept_id and location_id
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
AND d.location_id = e.location_id

To identify departments without any employees, we can perform a left join on multiple columns (dept_id and location_id) between the "departments" and "employees" tables and check if the "employee_id" column from the "employees" table is NULL.

Left join multiple columns to identify departments without any employees
SELECT d.dept_id
FROM departments d
LEFT JOIN employees e 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.


Let's break down the SQL query above step by step:

  1. SELECT d.dept_id, d.department_name: This part of the query selects the "dept_id" and "department_name" columns from the "departments" table, which will be displayed in the query result.

  2. FROM departments d: Here, we specify the source table for the data retrieval. The table "departments" is aliased as "d," which allows us to refer to this table using the alias throughout the rest of the query.

  3. LEFT JOIN employees e ON d.dept_id = e.dept_id AND d.location_id = e.location_id: This is the left join part of the query. It combines the "departments" table ("d") with the "employees" table ("e") based on two conditions: "dept_id" and "location_id."

  4. WHERE e.employee_id IS NULL: The WHERE clause filters the rows that satisfy a specific condition. In this case, we are checking if there's no matching employee for each department, and we do this by checking if the "employee_id" column from the "employees" table is NULL. If it is NULL, it means there is no matching employee for that department.

In summary, the query is designed to find departments that have no employees associated with them. It first selects the "dept_id" and "department_name" from the "departments" table and then performs a left join with the "employees" table based on "dept_id" and "location_id." The left join ensures that all rows from the "departments" table are included in the result, regardless of whether there is a matching employee in the "employees" table or not. The WHERE clause then filters out the rows where the "employee_id" is NULL, indicating that there is no employee for that department. The final result will be a list of departments that have no employees.