MySQL Right Outer Join is one of the Types, which is useful to return all the existing records (or rows) from the Right table, and match rows from the left table. All the Unmatched rows from the left table are filled with NULL Values.
The MySQL Right Outer join is also called Right Join. So it is optional for you to use the Outer Keyword.
MySQL Right Join Syntax
The basic syntax of MySQL Right Outer Join is as shown below:
SELECT Table1.Column(s), Table2.Column(s)
FROM Table1
RIGHT JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column
--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s)
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.Common_Column = Table2.Common_Column
Let us see the visual representation of the MySQL Right Outer join for better understanding.
From the above image, you can understand that the MySQL Right Outer join displays all the records present in Table B, and matching rows in Table A.
To demonstrate the Right Outer Join, we are going to use the Employ and Department tables present in our company Database. Data present in the employ table is:
Data present in the MySQL Department Table is:
MySQL Right Outer Join Examples
The following is the list of ways that we can use this Right Join to combine two tables or get information (records) from two or more tables.
MySQL Right Join Using Select *
The following right outer join Query displays all the columns present in the Department table and matching records from the employ table
USE company;
SELECT * FROM employ
RIGHT OUTER JOIN department
ON employ.DeptID = department.DeptID;
If you observe the screenshot below, MySQL right Outer Join is displaying 12 records from the department table and Employ table. But for the last two rows in the employ table are returning NULL Values. It is because the DeptID for those records in the Employ table are NULLS.
NOTE: The DeptID column is repeated twice, which might be annoying to the end-user. To avoid unwanted columns, I suggest you select individual column names. Please avoid the SELECT * Statement in the right outer Join.
MySQL Right Join Select Few Columns
As we said before, please use the required columns after the SELECT Statement to avoid unwanted columns.
USE company;
SELECT First_Name, Last_Name, Education,
Yearly_Income, Sales,
DepartmentName, Standard_Salary
FROM employ
RIGHT JOIN department
ON employ.DeptID = department.DeptID;
MySQL Right Join Handling Ambiguous Columns
The above right joins query work perfectly as long as the column names from both employ and Department are different like above. What happens if they had the same column names in both tables? Well, with the above-specified approach, you end up in a mess. Let us see how to resolve the issue.
Let me show you one practical example of the MySQL right join ambiguous column. As you can see, we are using the same right join query. However, we added DepID from the department table as an additional column.
USE company;
SELECT First_Name, Last_Name, Education,
Yearly_Income, Sales,
DeptID, DepartmentName, Standard_Salary
FROM employ
RIGHT JOIN department
ON employ.DeptID = department.DeptID;
As you can see, it is throwing an error: Ambiguous column DeptID. It is because the DeptID column is present in both tables, and the MySQL query doesn’t know which column you are asking it to display.
To resolve these kinds of issues in MySQL right outer join, you always have to use the table name before the column name. The following query uses the ALIAS table name before the column names. By this approach, we can inform the right join query that we are looking for DepID belonging to the department table.
We can simply write the above query as:
USE company;
SELECT emp.First_Name, emp.Last_Name, emp.Education,
emp.Yearly_Income, emp.Sales,
dept.DeptID, dept.DepartmentName, dept.Standard_Salary
FROM employ AS emp
RIGHT OUTER JOIN department AS dept
ON emp.DeptID = dept.DeptID;
NOTE: It is always a best practice to use the table name before the Column name in the right Outer Join. For example, SELECT employ.First_Name
Right Join Using Keyword
If the common column name in both tables is the same, then you can use the USING keyword. Here DeptID is the common column name in both tables, so the above query can also be returned as:
USE company;
SELECT emp.First_Name, emp.Last_Name, emp.Education,
emp.Yearly_Income, emp.Sales,
dept.DepartmentName, dept.Standard_Salary
FROM employ AS emp
RIGHT OUTER JOIN department AS dept
USING(DeptID);
MySQL Right Join using Multiple Conditions
Until now, we are showing the = operator as the condition to right join the employ and department table. But you can replace = with < (less than), > (greater than), or not equal to operators. In this example, we are using two conditions to the right join those two tables.
The following right join query displays all the records from the employ table and matching records from the department table. Here matching records means the DeptID of both tables should match, and yearly income should be greater than 800000
USE company;
SELECT empl.First_Name, empl.Last_Name, empl.Education,
empl.Yearly_Income, empl.Sales,
dept.DepartmentName, dept.Standard_Salary
FROM employ AS empl
RIGHT JOIN department AS dept
ON empl.DeptID = dept.DeptID AND
empl.Yearly_Income > 800000;
I think you are confused about the result. Let me replace the greater than symbol with less than. It means the DeptID of both tables should match, and yearly income should be less than 1000000
MySQL Right Join Where Clause Example
In this example, we show how to use the Where clause (to apply filter) along with Right Outer Join. I suggest you refer Where Clause article.
USE company;
SELECT empl.First_Name, empl.Last_Name, empl.Education,
empl.Yearly_Income, empl.Sales,
dept.DepartmentName, dept.Standard_Salary
FROM employ AS empl
RIGHT JOIN department AS dept
ON empl.DeptID = dept.DeptID
WHERE Standard_Salary > 750000;
Right Join Example Command prompt
Let me show you how to write the Right Outer Join query using the Command prompt. In this example, we also show how to use the Order By clause along with the MySQL Right Outer Join. I suggest you refer to the Order By article.
USE company;
SELECT empl.First_Name, empl.Last_Name, empl.Education,
empl.Yearly_Income, empl.Sales,
dept.DepartmentName, dept.Standard_Salary
FROM employ AS empl
RIGHT JOIN department AS dept
ON empl.DeptID = dept.DeptID
ORDER BY First_Name, Last_Name;
