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:

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

Left Join Multiple Columns: dept_id and location_id

Left join multiple columns: dept_id and location_id

SELECT d.dept_id
	,d.department_name
	,employee_id
	,first_name
	,last_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
	AND d.location_id = e.location_id

dept_id	department_name	employee_id	first_name	last_name
1	HR	101	John	Doe
1	HR	103	Michael	Johnson
2	Finance	102	Jane	Smith
3	IT	104	Emily	Williams
4	Marketing	null	null	null

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
	,d.department_name
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.

dept_id	department_name
4	Marketing

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.