Back to: MySQL Tutorials for Beginners and Professionals
Left Outer Join in MySQL with Examples
In this article, I am going to discuss the Left Outer Join in MySQL with Examples. Please read our previous article where we discussed Inner Join in MySQL with Examples. At the end of this article, you will understand the following pointers in detail.
- What is Left Outer Join?
- How to implement Left Outer Join in MySQL?
- When do we need to use Left JOIN?
- Examples to Understand Left Outer Join
- How to use Left Join along with Where Condition?
- How to retrieve only the non-matching rows from the left table?
- Joining three Tables in MySQL using Left Join
What is Left Outer Join in MySQL?
The Left Outer Join in MySQL is used to retrieve all the matching records from both the tables as well as 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 is used to perform the left join in MySQL.
The question that should come to your mind is which is the left table and which is the right table? The answer is, the table which is mentioned to the left of the LEFT OUTER JOIN keyword is the left table, and the table which is mentioned to the right of the LEFT OUTER JOIN keyword is the right table. The following diagram shows the pictorial representation of 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, then also it is going to perform 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 as well as 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. If this is not clear at the moment, then don’t worry, we will try to understand this with some real-time examples.
Example to Understand Left Outer Join
Let us understand how to implement Left Outer Join in MySQL with some Examples. To understand Left Outer Join in MySQL, we are going to use the following Employee, Address, and Projects tables. Here, EmployeeId is the common column that is available in all three tables, and based on this EmployeeId column we are going to 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, 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 there is an entry that 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 as well as 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, 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 exists in the Projects table i.e. two employees are not currently assigning with any projects. So, what our requirement is, we want to retrieve only those two employees for which there are 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. 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;
Once you execute the above SQL query, you will get the following result set. You can see, here, we get all the employee’s records irrespective of whether they are having Addresses or assigning with any projects or not.
In the next article, I am going to 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 need. I would like to have your feedback. Please post your feedback, question, or comments about this article.