MySQL RIGHT JOIN

MySQL right join links two tables with each other. The left table column connects with the complete right side table. Each row of the right table tries to connect with the left row. If the right table does not match the left table, the query returns a null value and when the right table matches with a left table then query return all row of the right table.

Syntax

The syntax of the right join shows below. This syntax works on two tables.

SELECT
Column1 data type constraint,
Column2 data type constraint,
Column3 data type constraint,
FROM table name1
RIGHT JOIN table2 ON condition1;

The syntax of the right join shows below. This syntax works on multiple tables.

SELECT
Column1 data type constraint,
Column2 data type constraint,
Column3 data type constraint,
FROM table name1
RIGHT JOIN table2 ON condition1
RIGHT JOIN table3 ON condition2;

Examples of the MySQL right join

1) Example: Right join with multiple table examples shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. Here, we have use "ON" condition.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> ON d.department_id = s.subject_id;

OUTPUT

The above output image shows the combined columns of the two tables. The "department_name" and "department_id" columns has in the department table. The "subject_name" and "students" columns have in the subject table.

2) Example: Right join with multiple table examples shows below.

Execute the below query to join two tables. Two tables join required columns using specified conditions. Here, the query selects entire columns of the table.

mysql> select *
    -> FROM department d
    -> RIGHT JOIN subject s
    -> ON d.department_id = s.subject_id;

OUTPUT

The above image shows all columns of both tables. For example, this query shows complete columns of the department and student tables.

3) Example: the Right join example shows below.

Execute the below query to join two tables. Two tables join all columns using the "RIGHT JOIN" query. This query uses ON and WHERE clause with RIGHT JOIN.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> ON d.department_id = s.subject_id
    -> WHERE subject_id < 3;

OUTPUT

The above image shows the combined columns of the two tables using the right join. Here, you can see four columns and two rows.

4) Example: Right joins with the "GROUP BY" clause example shows below.

Execute the below query to join two tables with required columns. Two tables join four columns using the "RIGHT JOIN" query. This query uses the "ON" and the "ORDER BY" clause with RIGHT JOIN.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> ON d.department_id = s.subject_id
    -> ORDER BY
    -> department_id DESC,
    -> subject_id DESC;

OUTPUT

The above image shows the combined columns of the two tables. Here, you can see four columns in descending order data.

5) Example: Right join with the "USING" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "RIGHT JOIN" query uses "USING" clause with the "subject_id" as a foreign key.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> USING (subject_id);

OUTPUT

The above output image displays combined columns of the department and subject tables. The "subject_id" column links two tables using a foreign key. The above table works right join query with the "USING" clause. Here, the table shows data using a common column or foreign key.

6) Example: Right joins with the "GROUP BY" clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "RIGHT JOIN" query uses "GROUP BY" clause with the "subject_id" foreign key. It also uses AND logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> USING (subject_id)
    -> WHERE d.department_name = "Bachelor of Technology"
    -> GROUP BY subject_id;

OUTPUT

The above output image displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key.

7) Example: Right join with the multiple clause example shows below.

Execute the below query to join two tables. Two tables join required columns using conditions. For example, the "RIGHT JOIN" query uses "ORDER BY" and "using" clauses with the "subject_id" foreign key. It also uses OR logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> RIGHT JOIN subject s
    -> USING (subject_id)
    -> WHERE d.department_name = "Bachelor of Technology"
    -> OR  s.subject_name = "Computer Science"
    -> ORDER BY department_id DESC, subject_id DESC;

OUTPUT

The above output table displays combined columns of the department and subject tables. The "subject_id" column links two tables using a foreign key. The above table works right join query with the "USING" clause. Here, the table applies the right join query with the "ORDER BY" clause. The table data display in descending order.