SQL Right Join

The SQL Right Join query displays all the table records and similar records from the left table. The query display zero records if it doesn’t find any similar records. If it doesn’t find similar records in the left table, it returns the NULL keyword in the right table row.

Syntax of the SQL Right Join

SELECT Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5 FROM Table_1 Right JOIN Table_2 ON Expression;  

Column_Name1, Column_Name2, Column_Name3, Column_Name4 columns from both the table.

Right Table: Table_2, Left Table: Table1.

Expression: Condition expression with ON clause.

This conditional expression will be a comparison condition of comparison operators and logical operators of SQL. Remember that Comparison Operators are >, <, >=, <=, =, !=, LIKE, BETWEEN, etc., and Logical Operators are AND, OR, and NOT.

There is One too Many relationships between the Student and the Department table. One to Many relationships means that one Department can be allocated to one Student or more than one Student. When we execute the SQL Right Join query on these tables on Students_Id and Department_Id, all the records of the student table are displayed in the result set with the similar records in the Department records.

Department is not allocated to any students in the result set with the NULL keyword from the left side table.  

Example of Right Join in Structured Query Language:

Table Number 1: Diploma_Student

Table Number 2: Department

Example 1: Execute a right join query on the Diploma_Student and Department table name.

SELECT Student_Id, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Fifth_Sem, Sixth_Sem, Total, D.Department_Id, Department_Name FROM Diploma_Student DS RIGHT JOIN Department D ON DS.Department_Id = D.Department_Id;

The above Right join query joins the Diploma_Student table and Department table and retrieves the data from the tables where DS.Department_Id = D.Department_Id.

The output of the above query is as follows:

Example 2: Execute a right join query on the Diploma_Student and Department table name using the WHERE Clause.

SELECT Student_Id, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Fifth_Sem, Sixth_Sem, Total, D.Department_Id, Department_Name FROM Diploma_Student DS RIGHT JOIN Department D ON DS.Department_Id = D.Department_Id WHERE D.Department_Id IN (1, 3, 4, 6, 8);

The above Right join query joins the Diploma_Student table and Department table and retrieves the data from the tables where DS.Department_Id = D.Department_Id. The Student records show where department id is 1, 3, 4, 6, and 8. We have applied the WHERE clause condition on the Department id of the Department table.

The output of the above query is as follows:

Example 3: Execute a right join query on the Diploma_Student and Department table name using the ORDER BY Clause.

SELECT Student_Id, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Fifth_Sem, Sixth_Sem, Total, D.Department_Id, Department_Name FROM Diploma_Student DS RIGHT JOIN Department D ON DS.Department_Id = D.Department_Id ORDER BY D.Department_Id;

The above Right join query joins the Diploma_Student table and Department table and retrieves the data from the tables where DS.Department_Id = D.Department_Id, and the Student's records will display in the ascending order as we have used the ORDER BY clause in the query.

The output of the above query is as follows: