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

Student_Id	Student_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id
202111	Vaishnavi Patil	94	91	88	85	95	92	91	1
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Axar Patel	85	80	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	80	75	78	80	76	77	5
202118	Sakashi Patil	80	78	74	78	80	77	78	2
202119	Sopan Bhore	70	68	75	75	80	80	75	2
202220	Prajwal Lokhande	80	85	85	75	78	80	81	4
202221	Anuja Wanare	85	88	86	82	84	85	85	5
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	75	1
202224	Aakash Jain	80	75	72	74	85	80	78	4
202225	Akshay Agarwal	85	80	78	88	90	82	84	5

Table Number 2: Department

Department_Id	Department_Name
1	Computer Engineering
2	Information Technology
3	Mechanical Engineering
4	Automobile Engineering
5	Civil Engineering
6	Electrical Engineering
7	Electronics and Telecommunication Engineering
8	Chemical Engineering

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:

Student_Id	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id	Department_Name
202111	94	91	88	85	95	92	91	1	Computer Engineering
202115	85	80	82	86	92	84	85	1	Computer Engineering
202223	70	75	71	74	80	78	75	1	Computer Engineering
202112	85	90	92	80	85	82	86	2	Information Technology
202118	80	78	74	78	80	77	78	2	Information Technology
202119	70	68	75	75	80	80	75	2	Information Technology
202113	90	88	94	87	85	90	89	3	Mechanical Engineering
202116	78	75	80	74	85	77	78	3	Mechanical Engineering
202222	90	89	87	90	92	91	90	3	Mechanical Engineering
202114	95	90	92	88	92	90	91	4	Automobile Engineering
202220	80	85	85	75	78	80	81	4	Automobile Engineering
202224	80	75	72	74	85	80	78	4	Automobile Engineering
202117	75	80	75	78	80	76	77	5	Civil Engineering
202221	85	88	86	82	84	85	85	5	Civil Engineering
202225	85	80	78	88	90	82	84	5	Civil Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	6	Electrical Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	7	Electronics and Telecommunication Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	Chemical Engineering

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:

Student_Id	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id	Department_Name
202111	94	91	88	85	95	92	91	1	Computer Engineering
202115	85	80	82	86	92	84	85	1	Computer Engineering
202223	70	75	71	74	80	78	75	1	Computer Engineering
202113	90	88	94	87	85	90	89	3	Mechanical Engineering
202116	78	75	80	74	85	77	78	3	Mechanical Engineering
202222	90	89	87	90	92	91	90	3	Mechanical Engineering
202114	95	90	92	88	92	90	91	4	Automobile Engineering
202220	80	85	85	75	78	80	81	4	Automobile Engineering
202224	80	75	72	74	85	80	78	4	Automobile Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	6	Electrical Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	Chemical Engineering

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:

Student_Id	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Fifth_Sem	Sixth_Sem	Total	Department_Id	Department_Name
202111	94	91	88	85	95	92	91	1	Computer Engineering
202115	85	80	82	86	92	84	85	1	Computer Engineering
202223	70	75	71	74	80	78	75	1	Computer Engineering
202112	85	90	92	80	85	82	86	2	Information Technology
202118	80	78	74	78	80	77	78	2	Information Technology
202119	70	68	75	75	80	80	75	2	Information Technology
202113	90	88	94	87	85	90	89	3	Mechanical Engineering
202116	78	75	80	74	85	77	78	3	Mechanical Engineering
202222	90	89	87	90	92	91	90	3	Mechanical Engineering
202114	95	90	92	88	92	90	91	4	Automobile Engineering
202220	80	85	85	75	78	80	81	4	Automobile Engineering
202224	80	75	72	74	85	80	78	4	Automobile Engineering
202117	75	80	75	78	80	76	77	5	Civil Engineering
202221	85	88	86	82	84	85	85	5	Civil Engineering
202225	85	80	78	88	90	82	84	5	Civil Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	6	Electrical Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	7	Electronics and Telecommunication Engineering
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	Chemical Engineering