MySQL Right Join

Last Updated: 29 June, 2023

In MySQL, a right join is a type of join operation that returns all the rows from the right (or second) table and the matching rows from the left (or first) table. If there is no match, NULL values are returned for the columns of the left table.

The syntax for a right join in MySQL is as follows:

 SELECT column_list
 FROM table1
 RIGHT JOIN table2
 ON table1.column = table2.column;

Here's a breakdown of the syntax:

• SELECT: Specifies the columns we want to retrieve from the joined tables.
• column_list: The columns we want to select from the tables. We can specify multiple columns separated by commas or use the wildcard (*) to select all columns.
• table1 and table2: The names of the tables we want to join.
• ON: Specifies the condition for joining the tables. It defines the columns that should be used to match the rows between the two tables.

Let's consider an example to understand it better. Suppose we have two tables, employees and departments, with the following structures:

Table: employees

  +----+----------+------------+
  | id | name     | department |
  +----+----------+------------+
  | 1  | John     | 1          |
  | 2  | Sarah    | 2          |
  | 3  | Michael  | 1          |
  +----+----------+------------+

Table: departments

  +----+------------+
  | id | name       |
  +----+------------+
  | 1  | Sales      |
  | 2  | Marketing  |
  | 3  | Finance    |
  +----+------------+

We can use a right join to retrieve all the employees along with their department names:

 SELECT employees.name, departments.name AS department_name
 FROM employees
 RIGHT JOIN departments
 ON employees.department = departments.id;

The result of the query will be:

  +------+----------------+
  | name | department_name|
  +------+----------------+
  | John | Sales          |
  | Sarah| Marketing      |
  | Mich | Sales          |
  +------+----------------+  

In this example, the right join returned all the rows from the departments table and matched them with the corresponding rows from the employees table based on the department and id columns, respectively. The employee named "Michael" has a department ID of 1, which matches the Sales department in the departments table. The right join ensures that all departments are included in the result, even if there are no matching employees.