Left Outer Join in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Left Outer Join in MySQL with Examples

In this article, I will discuss the Left Outer Join in MySQL with Examples. Please read our previous article discussing Inner Join in MySQL with Examples. You will understand the following pointers in detail at the end of this article.

1. What is Left Outer Join?
2. How to implement Left Outer Join in MySQL?
3. When do we need to use Left JOIN?
4. Examples to Understand Left Outer Join
5. How to use Left Join along with Where Condition?
6. How do we retrieve only the non-matching rows from the left table?
7. Joining three Tables in MySQL using Left Join

What is Left Outer Join in MySQL?

The Left Outer Join in MySQL retrieves all the matching records from both tables and non-matching records from the left side table. In that case, the non-matching data will take a null value. The LEFT OUTER JOIN or LEFT JOIN keyword performs the left join in MySQL.

The question that should come to your mind is, which is the left and right table? The answer is the table mentioned to the left of the LEFT OUTER JOIN keyword is the left table, and the table mentioned to the right of the LEFT OUTER JOIN keyword is the right table. The following diagram shows the pictorial representation of the Left Outer Join.

How to implement Left Outer Join in MySQL?

The following images show the syntax of MySQL Left Outer Join. As you can see in the below image, we can use either the LEFT OUTER JOIN or LEFT JOIN keyword to perform the Left Join operation. That means if you use the LEFT JOIN keyword, it will also perform the Left Outer JOIN Operation.

When do we need to use Left JOIN in MySQL?

If you want to retrieve all the matching rows from both the tables involved in the join and all the non-matching rows from the left side table in the result set, then you need to use Left Join in MySQL. In that case, the non-matching rows will take a null value. 

Example to Understand Left Outer Join in MySQL

Let us understand how to implement Left Outer Join in MySQL with some Examples. To understand Left Outer Join in MySQL, we will use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column available in all three tables, and based on this EmployeeId column, we will perform the Left Outer Join Operations.

Please use the below SQL Script to create the Company database and Employee, Address, and Projects tables with the required test data. Here, the EmployeeId column in the Address and Projects table is a foreign key referencing the EmployeeId column of the Employee Table.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  EmployeeId INT PRIMARY KEY,
  FullName VARCHAR(50) NOT NULL,
  Technology VARCHAR(50) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(50) NOT NULL,
  Age INT NOT NULL
);

INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1001, 'John Doe', 'Java', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1002, 'Mary Smith', 'Java', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1003, 'James Brown', 'Java', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1004, 'Mike Walker', '.NET', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1005, 'Linda Jones', '.NET', 75000, 'Female', 26);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1006, 'Anurag Mohanty', '.NET', 35000, 'Male', 25);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1007, 'Priyanla Dewangan', '.NET', 45000, 'Female', 27);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1008, 'Sambit Mohanty', '.NET', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1009, 'Pranaya Kumar', 'Java', 50000, 'Male', 28);
INSERT INTO Employee (EmployeeId, FullName, Technology, Salary, Gender, Age) VALUES (1010, 'Hina Sharma', 'Java', 75000, 'Female', 26);

CREATE TABLE Projects (
 ProjectId INT PRIMARY KEY AUTO_INCREMENT,
    ProjectName VARCHAR(200) NOT NULL,
 EmployeeId INT,
    StartDate DATETIME,
    EndDate DATETIME,
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);

INSERT INTO Projects (ProjectName, EmployeeId, StartDate, EndDate) VALUES 
('Develop Ecommerse Website from scratch', 1003, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('WordPress Website for our company', 1002, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Manage our Company Servers', 1007, NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
('Hosting account is not working', 1009, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('MySQL database from my desktop application', 1010, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY)),
('Develop new WordPress plugin for my business website', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY)),
('Migrate web application and database to new server', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY)),
('Android Application development', 1004, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('Hosting account is not working', 1001, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('MySQL database from my desktop application', 1008, NOW(), DATE_ADD(NOW(), INTERVAL 15 DAY)),
('Develop new WordPress plugin for my business website', NULL, NOW(), DATE_ADD(NOW(), INTERVAL 10 DAY));

CREATE TABLE Address
(
    AddressId INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeId INT,
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId)
);

INSERT INTO Address (EmployeeId, Country, State, City) Values (1001, 'India', 'Odisha', 'BBSR');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1002, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1003, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1004, 'India', 'Odisha', 'Cuttack');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1005, 'India', 'Maharashtra', 'Nagpur');
INSERT INTO Address (EmployeeId, Country, State, City) Values (1006, 'India', 'Odisha', 'BBSR');

MySQL Left Outer Join Example:

Our requirement is to retrieve the EmployeeId, FullName, Technology, Gender, and ProjectName from the Employee and Projects tables. The query should return the following result set. As you can see in the below image, we want to retrieve all the employees from the Employee table irrespective of whether an entry exists or not in the Projects table.

Following is the SQL Query that is Left Outer Joining the Employee and Projects tables based on the EmployeeID column and will give you the required results as shown in the above image.

SELECT Employee.EmployeeID, FullName, Technology, Gender, ProjectName
FROM Employee 
LEFT OUTER JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId;

If you look at the output here, we got 10 rows, i.e., all the records from the Employee table. Those employees who do not have any entry in the Projects table will have null values. So, this proves the left outer join in MySQL will retrieve all the matching rows from both the table and non-matching rows from the left-hand side table. In our example, Employee is the left-hand side table.

In MySQL, instead of using the LEFT OUTER JOIN keyword, you can also use the LEFT JOIN keyword as shown in the below SQL Query, and it will also give you the same result as the previous SQL Query.

SELECT Employee.EmployeeID, FullName, Technology, Gender,  ProjectName
FROM Employee 
LEFT JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId;

Left Outer Join with Where Condition:

Let us understand how to use Left Outer join along with the WHERE filtering condition in MySQL. Our requirement is to retrieve all the EmployeeId, FullName, Technology, Gender, and ProjectName from the Employee and Projects tables by using the Left Outer Join where the gender is Male. The following is the SQL Script.

SELECT Employee.EmployeeID, FullName, Technology, Gender, ProjectName
FROM Employee 
LEFT OUTER JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Employee.Gender = 'Male';

Once you execute the above SQL query, you will get the following result set, which includes only the Male employees.

How to retrieve only the non-matching rows from the left table in MySQL?

There are two employees for which there is no entry in the Projects table, i.e., two employees are not currently assigned any projects. So, our requirement is to retrieve only those two employees for which non-matching records exist in the Projects table. So, basically, we want to retrieve only the non-matching records from the left side table. The query should return the following result set.

The following Left Outer Join Query with the where clause does the same.

SELECT Employee.EmployeeID, FullName, Technology, Gender, ProjectName
FROM Employee 
LEFT JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId
WHERE Projects.EmployeeId IS NULL;

Joining three Tables in MySQL using Left Outer Join:

It is also possible in MySQL to join more than two tables. Let us see and understand how to JOIN three tables in MySQL using Left Outer Join. The following is the syntax in MySQL to join three tables using Left Outer Join.

Example: Left Joining Employee, Projects, and Address tables

Now, our requirement is to fetch the details of all employees along with the project name and address if they have one. The following SQL Query Left Joining Employee, Projects, and Address tables to fetch the Employee details along with their projects and address.

SELECT Employee.EmployeeID, FullName, Technology, Gender, ProjectName, Country, State, City
FROM Employee 
LEFT OUTER JOIN Projects ON Employee.EmployeeID = Projects.EmployeeId
LEFT JOIN Address ON Employee.EmployeeID = Address.EmployeeId;

You will get the following result set once you execute the above SQL query. You can see here that we get all the employees’ records irrespective of whether they have Addresses or are assigned to any projects.

When to Use Left Outer Join in MySQL?

A Left Outer Join in MySQL is used in several specific scenarios where you need to select records from one table (left table) along with the matching records from another table (right table) and also include all records from the left table even if there are no matching records in the right table. Here are some typical situations when a Left Outer Join is appropriate:

• Include All Records from the Primary Table: When you need to retrieve all records from one table regardless of whether they have related records in another table. For example, listing all employees, including those who aren’t assigned to any department.
• Identify Unmatched Records: To identify records in the left table that don’t have a corresponding match in the right table. For instance, finding all products that have never been ordered.
• Default or Fallback Values: When you want to provide default values for records that don’t have a match in the joined table. For example, assigning a default department to employees who aren’t yet assigned to any specific department.
• Comprehensive Reports: In creating reports where you need a complete list from one table along with any related information from another table. This ensures the report is comprehensive and includes all possible data from the primary table.
• Data Analysis and Cleanup: When analyzing data to find discrepancies, missing data, or for data cleanup purposes. For instance, identifying customers who haven’t made any purchases.
• Hierarchical Data: In scenarios where you’re dealing with hierarchical data, you need to fetch all parent records, regardless of whether they have child records.

In the next article, I will discuss Right Outer Join in MySQL with real-time examples. Here, in this article, I try to explain the Left Outer Join in MySQL with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this article.