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
- What is SQL Right Join?
- Right Join with the WHERE clause
- Right Join with GROUP BY and ORDER BY
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
SELECT table1.column_name1, table2.column_name1,.....FROM table1RIGHT JOIN table2ON 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:
Must Read: UPDATE query in SQL
Example: Join the Student and Faculty table to extract the record from both the table where Student.DepartmentID = Faculty.DepartmentID.
SELECT Student.StudentID, Student.Name, Faculty.FacultyNameFROM StudentRIGHT JOIN FacultyON Student.DepartmentID = Faculty.DepartmentID;
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.
SELECT Student.StudentID, Student.Name, Faculty.FacultyNameFROM StudentRIGHT JOIN FacultyON Student.DepartmentID = Faculty.DepartmentIDWHERE Student.StudentID IS NULL;
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.
SELECT d.DepartmentID, d.DepartmentName, count(s.Name) AS Number of StudentFROM Student sRIGHT JOIN Department dON s.DepartmentID = d.DepartmentIDGROUP BY d.DepartmentNameORDER BY d.DepartmentID;
|DepartmentID||Department Name||Number of Student|
Must Read: SQL LIMITS
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.
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Download this article as PDF to read offlineDownload as PDF