SQL RIGHT JOIN – Example and Syntax
Right Join in the sql returns all the rows from the right table and the matching entries from both tables. In this article, we will discuss how to use right join in sql with WHERE clause, GROUP BY clause, and ORDER BY clause.
In the previous articles, we have briefly discussed left join and inner join in SQL with the help of examples. Now, in this article, we will discuss another type of join in SQL, i.e., right join. Similar to the Left and Inner joins in SQL, it is used to extract the data from two or more tables in a little different way.
So, let’s move and explore how right join in SQL works.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is SQL Right Join?
Joins in SQL is used to join two tables based on a common column. Right Join returns all the values from the right table and the matching entries from the left table, and if there are some rows that are in the right table but not in the left table, then that row will also be included with the NULL entries.
In simple terms,
- Right Join returns all the rows from the right table
- If there is any row in the left table but not in the right table, then that row will not be included in the result
The below image shows how Right Join works in SQL.
In the above figure, we have two tables, Student (left) and Department (right), and when we right join both the tables, then:
- All the rows of the department table (right) are included.
- All the values in the name column are NULL except the Student ID 1004 (since this is the only matching row in both the table)
- Only one row of the Student table (left) is included (Student Id 1004)
Must Read: SQL SELECT
Must Read: Introduction to SQL
Syntax
SELECT table1.column_name1, table2.column_name1,.....FROM table1RIGHT JOIN table2 ON table1.matching_column_name = table2.matching_column_name;
Now, let’s have some examples to get a better understanding of SQL Right Join.
Let us have three tables, Student, Department, and Faculty tables:
| StudentID | Name | Percentage(%) | DepartmentID | |
| 1001 | Ajay | ajay@xyz.com | 85 | 1 |
| 1002 | Babloo | babloo@xyz.com | 67 | 2 |
| Chhavi | chhavi@xyz.com | 89 | 3 | |
| Dheeraj | dheeraj@xyz.com | 75 | 4 | |
| 1005 | Evina | evina@xyz.com | 91 | 6 |
| 1006 | Krishna | krishna@xyz.com | 99 | 7 |
| 1007 | vikram | vikram@xyz.com | 55 | 2 |
| DepartmentID | DepartmentName |
| 1 | Mathematics |
| 2 | Physics |
| 3 | English |
| DepartmentID | FacultyName |
| 1 | Piyush |
| 2 | Namita |
| 3 | Ashneer |
| 4 | Ghazal |
| 5 | Anupam |
Must Read: UPDATE query in SQL
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views
Example: Join the Student and Faculty table to extract the record from both the table where Student.DepartmentID = Faculty.DepartmentID.
Query
SELECT Student.StudentID, Student.Name, Faculty.FacultyNameFROM StudentRIGHT JOIN Faculty ON Student.DepartmentID = Faculty.DepartmentID;
Output
| StudentID | Name | Faculty |
| 1001 | Ajay | Piyush |
| 1002 | Babloo | Namita |
| NULL | Chhavi | Ashneer |
| NULL | Dheeraj | Ghazal |
| NULL | NULL | Anupam |
| 1007 | Vikram | Namita |
Must Read: SQL Logical Operator
Must Read: Introduction to Normalization
Right Join with WHERE clause
Example: Join the Student and Faculty table to extract the record from both the table where Student.DepartmentID = Faculty.DepartmentID where Student ID is NULL.
Query
SELECT Student.StudentID, Student.Name, Faculty.FacultyNameFROM StudentRIGHT JOIN Faculty ON Student.DepartmentID = Faculty.DepartmentIDWHERE Student.StudentID IS NULL;
Output
| StudentID | Name | Faculty |
| NULL | Chhavi | Ashneer |
| NULL | Dheeraj | Ghazal |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
Right Join with GROUP BY and ORDER BY
Example: Count the number of students in each department using student and department tables.
Query
SELECT d.DepartmentID, d.DepartmentName, count(s.Name) AS Number of StudentFROM Student sRIGHT JOIN Department d ON s.DepartmentID = d.DepartmentIDGROUP BY d.DepartmentNameORDER BY d.DepartmentID;
Output
| DepartmentID | Department Name | Number of Student |
| 1 | Mathematics | 1 |
| 2 | Physics | 2 |
| 3 | English | 1 |
Must Read: SQL LIMITS
Must Check: Database and SQL Online Course and Certifications
Conclusion
In this article, we have briefly discussed right join in sql with the help of examples.
Hope this article, will help you in your data science/data analysis journey.
Keep Learning!!
Keep Sharing!!
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
