Using Left Join/Right Join to Join Two Tables on a Common Column, and to Default to Null For Missing Values in MySQL

index.md

Using Left Join/Right Join to Join Two Tables on a Common Column, and to Default to Null For Missing Values in MySQL

The following SQL schema will produce the two tables.

Create table If Not Exists Employees (id int, name varchar(20));
Create table If Not Exists EmployeeUNI (id int, unique_id int);
Truncate table Employees;
insert into Employees (id, name) values ('1', 'Alice');
insert into Employees (id, name) values ('7', 'Bob');
insert into Employees (id, name) values ('11', 'Meir');
insert into Employees (id, name) values ('90', 'Winston');
insert into Employees (id, name) values ('3', 'Jonathan');
Truncate table EmployeeUNI;
insert into EmployeeUNI (id, unique_id) values ('3', '1');
insert into EmployeeUNI (id, unique_id) values ('11', '2');
insert into EmployeeUNI (id, unique_id) values ('90', '3');

Employees

id	name
1	Alice
7	Bob
11	Meir
90	Winston
3	Jonathan

EmployeeUNI

id	unique_id
3	1
11	2
90	3

Because these two tables share a common column that contains similar data (id) we can join these tables based on this column.

We can join these tables on their id columns.

For a LEFT JOIN, the initial table that is declared after FROM is the table "on the left". For this particular example, EmployeeUNI has only three rows, so the LEFT JOIN will take these three rows and attempt to join them with values in the Employees table, using the id column as a common column between the two tables.

SELECT EmployeeUNI.unique_id,
  Employees.name
FROM EmployeeUNI
  LEFT JOIN Employees ON EmployeeUNI.id = Employees.id;

unique_id	name
2	Meir
3	Winston
1	Jonathan

On the other hand, if we do a RIGHT JOIN, this will use the table "on the right" as the starting point. This would be the Employees table. This table has 5 total rows, and doing a RIGHT JOIN will attempt to match each of its name values to a unique_id in the EmployeeUNI table. Because certain name values do not have a matching unique_id, we receive a null for these values.

SELECT EmployeeUNI.unique_id,
  Employees.name
FROM EmployeeUNI
  RIGHT JOIN Employees ON EmployeeUNI.id = Employees.id;

unique_id	name
1	Jonathan
2	Meir
3	Winston
null	Alice
null	Bob

But, we can still get this same behavior out of LEFT JOIN. We just need to reverse which table is on the left and which table is on the right.

With the Employees table now on the left in the query below, we are attempting to join values from EmployeeUNI to the five existing rows in Employees, which will result in null values.

SELECT EmployeeUNI.unique_id,
  Employees.name
FROM Employees
  LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id;

unique_id	name
1	Jonathan
2	Meir
3	Winston
null	Alice
null	Bob