RIGHT JOIN

(return all rows from the right table and matching rows from the left table or null if no matching rows are found in the left table.)

MySQL RIGHT JOIN is similar to LEFT JOIN, except that the treatment of the joined tables is reversed.

Here’s the syntax of the RIGHT JOIN of two tables t1 and t2:

SELECT
Table1.column1,
Table2.column2
FROM
Table1
RIGHT JOIN
Table2 ON Table1.common_column = Table2.common_column
WHERE
Table1.some_column = 'some_value'
ORDER BY
Table1.column1 DESC;

MySQL RIGHT JOIN clause examples

We’ll use the tables employees and customers from the sample database for the demonstration:

The column salesRepEmployeeNumber in the table customers links to the column employeeNumber in the employees table.

A sales representative, or an employee, may be in charge of zero or more customers. And each customer is taken care of by zero or one sales representative.

If the value in the column salesRepEmployeeNumber is NULL, which means the customer does not have any sales representative.

1) Simple MySQL RIGHT JOIN example

This statement uses the RIGHT JOIN clause join the table customers with the table employees.

SELECT 
    employeeNumber, 
    customerNumber
FROM
    customers
RIGHT JOIN employees 
    ON salesRepEmployeeNumber = employeeNumber
ORDER BY 
	employeeNumber;

2) Using MySQL RIGHT JOIN to find unmatching rows

The following statement uses the RIGHT JOIN clause to find employees who are not in charge of any customers:

SELECT
    employeeNumber,
    customerNumber
FROM
    customers
RIGHT JOIN employees ON
	salesRepEmployeeNumber = employeeNumber
WHERE customerNumber is NULL
ORDER BY employeeNumber;Code language: SQL (Structured Query Language) (sql)

Summary

• MySQL RIGHT JOIN allows you to query data from two or more related tables.
• The RIGHT JOIN starts selecting rows from the right table. It always returns rows from the right table whether or not there are matching rows in the left table.
• The RIGHT OUTER JOIN is the synonym of the RIGHT JOIN.