MySQL : RIGHT JOIN Keyword

In MySQL, the RIGHT JOIN keyword is used to retrieve all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

Here's the basic syntax of the RIGHT JOIN:


SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
 

  • columns: The columns you want to retrieve from the tables.
  • table1: The left table.
  • table2: The right table.
  • column_name: The column(s) that exist in both tables and are used to join them.

Example:

Let's say we have two tables: employees and departments. The employees table contains information about employees, including their department ID, and the departments table contains department names and their corresponding department IDs.

We want to retrieve the names of all departments along with the names of the employees who belong to each department. If a department does not have any employees, we still want to include its information in the result set, with the employee names as NULL.


SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
 

In this query:

  • employees is the left table.
  • departments is the right table.
  • We specify the join condition using the ON keyword, which matches the department_id column from the employees table with the department_id column from the departments table.
  • The RIGHT JOIN ensures that all rows from the departments table are included in the result set, regardless of whether there is a match in the employees table.
  • If a department does not have any employees (i.e., there is no matching record in the employees table), the employee names will be NULL in the result set.

RIGHT JOIN is less commonly used compared to LEFT JOIN, but it serves a similar purpose. It ensures that all records from the right table are included in the result set, even if there are no matches in the left table.