Right Outer Join in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Right Outer Join in MySQL with Examples

In this article, I am going to discuss the Right Outer Join in MySQL with Examples. Please read our previous article where we discussed Left Outer Join in MySQL with Examples. At the end of this article, you will understand the following pointers in detail.

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

What is Right Outer Join in MySQL?

The Right Outer Join in MySQL is used to retrieve all the matching records from both the tables as well as all the non-matching records from the right-hand side table. In that case, the non-matching data will take a null value. The RIGHT OUTER JOIN or RIGHT JOIN keyword is used to perform the right join operation 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 RIGHT OUTER JOIN keyword is the left table, and the table which is mentioned to the right of the RIGHT OUTER JOIN keyword is the right table. The following diagram shows the pictorial representation of Right Outer Join.

How to implement Right Outer Join in MySQL?

The following images show the syntax of MySQL Right Outer Join. As you can see in the below image, we can use either the RIGHT OUTER JOIN or RIGHT JOIN keyword to perform the Right Join operation. That means if you use the RIGHT JOIN keyword, then also it is going to perform Right 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 right-hand side table in the result set then you need to use Right Outer Join in MySQL. In that case, the non-matching rows will take a null value.

Example to Understand Right Outer Join in MySQL

Let us understand how to implement Right Outer Join in MySQL with Examples. To understand Right 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 Right 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 to 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 Right Outer Join Example:

Our requirement is to retrieve the EmployeeId, FullName, Technology, Gender, ProjectName from the Employee and Projects tables. The SQL query should return the following result set. As you can see in the below image, we want to retrieve all the Project details from the Projects table irrespective of whether the project is assigned to any employee or not.

Following is the SQL Query that is Right 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 
RIGHT OUTER JOIN Projects 
ON Employee.EmployeeID = Projects.EmployeeId;

If you look at the output, here, we got 11 rows i.e. all the records from the Projects table. Those Projects which is not assigned to any employee will have null values. So, this proves, the right outer join in MySQL will retrieve all the matching rows from both the table as well as non-matching rows from the right-hand side table. In our example, Projects is the left-hand side table.

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

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

MySQL Right Outer Join with Where Condition:

Let us understand how to use Right Outer join with the WHERE filtering condition. Our requirement is to retrieve all the Projects along with the Employee details from the Employee and Projects tables by using the Right Outer Join where the gender is Male. The following is the SQL Script.

SELECT Projects.ProjectId, ProjectName, Employee.EmployeeID, FullName, Technology, Gender 
FROM Employee 
RIGHT 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 right table in MySQL?

There are three projects which are not assigned to any employee. So, what our requirement is, we want to retrieve only those three projects from the Projects table. So, basically, we want to retrieve only the non-matching records from the right-side table. The query should return the following result set.

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

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

In the next article, I am going to discuss Self Join in MySQL with Examples. Here, in this article, I try to explain the Right 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.