MySQL Right Join

MySQL Right Outer Join is one of the Join Type, which is useful to return all the existing records (or rows) from the Right table, and matching rows from the left table. All the Unmatched rows from the left table filled with NULL Values.

The MySQL Right Outer join also called Right Join. So it is optional for you to use the Outer Keyword

MySQL Right Join Syntax

The basic syntax of Right outer Join in MySQL is as shown below:

-- SQL Server Right JOIN Syntax
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.

MySQL RIGHT JOIN 1

From the above image, you can understand that the Right Outer join displays all the records present in Table B, and matching rows in Table A.

To demonstrate the MySQL Right Outer Join, we are going to use the Employ and Department tables present in our company Database. Data present in employ table is:

MySQL Right Outer Join 20

Data present in the MySQL Department Table is:

MySQL Right Outer Join 21

MySQL Right Join Examples

The following are the list of ways that we can use this MySQL Right Outer 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 display all the columns present in Department table, and matching records from employ table

-- MySQL Right Outer Join Example
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 employ table is returning NULL Values. It is because the DeptID for those records in the Employ table are NULLS.

MySQL RIGHT JOIN 2

NOTE: The DeptID column repeated twice, which might be annoying to the end-user. To avoid unwanted columns, I suggest you select individual column names. Please avoid SELECT * Statement in 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

-- MySQL Right Outer Join Example
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 3

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 the 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 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.

-- MySQL Right Outer Join Example
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, DeptID column is present in both tables, and MySQL query doesn’t know which column you are asking it to display.

MySQL RIGHT JOIN 4

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 is using 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:

-- MySQL Right Outer Join Example
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;
MySQL RIGHT JOIN 5

NOTE: It is always a best practice to use the table name before the Column name in right Outer Join. For example, SELECT employ.First_Name

MySQL Right Join Using Keyword

If the common column name in both the tables is the same, then you can use the USING keyword. Here DeptID is the common column name in both the tables, so above query can also be returned as:

-- MySQL Right Outer Join Example
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 6

MySQL Right Join using Multiple Conditions

Until now, we are showing the = operator as the condition to right join 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 right join those two tables.

The following right join query display all the records from the employ table, and matching records from department table. Here matching records means, DeptID of both tables should match, and yearly income should be greater than 800000

-- MySQL Right Outer Join Example
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;
MySQL RIGHT JOIN 7

I think you are confused about the result. Let me replace the greater than symbol with less than. It means, DeptID of both tables should match, and yearly income should be less than 1000000

MySQL RIGHT JOIN 8

Right Join Where Clause Example

In this example, we show how to use Where clause (to apply filter) along with Right Outer Join. I suggest you refer MySQL Where Clause article.

-- MySQL Right Outer Join Example
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;
MySQL RIGHT JOIN 9

MySQL Right Join Example Command prompt

Let me show how to write Right Outer Join query using Command prompt. In this example, we also show how to use Order By clause along with the Right Outer Join. I suggest you refer to MySQL Order By article.

-- MySQL Right Outer Join Example
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;
MySQL RIGHT JOIN 10