Table of Contents
SQL RIGHT JOIN Overview
SQL RIGHT JOIN gives all the matching rows plus all the rows in the right table which are not in the left table. It's similar to LEFT JOIN. Matching means both tables have the values. The below figure shows the behavior of the RIGHT JOIN.
A RIGHT JOIN is equivalent to a RIGHT OUTER JOIN. SQLite does not support RIGHT JOIN.
SQL RIGHT JOIN Examples
Now, we will discuss it through examples where we have two table employee table and a department table. Our objective is to find which employee belongs to which department.
Example-1: RIGHT JOIN Two Tables
The employee table has three columns. These columns are employee number, employee name, and department number. Employee number is the primary key. And department number is the foreign key.
The department table has three columns. These columns are department number, department location, department name. Here department number is the primary key.
In the above table, table#1 is the left table, and table#2 is the right table. Now, in the above employee table, we have 3 department numbers 1,2,3. And in the department table, we have four department numbers. Now we will check what are the results going to be.
The query for the SQL RIGHT JOIN is:
Select (Emp_no, Emp_name, Dep_location, Dep _name from emp Right join dept On ( emp.dept_no= dept.dept_no));
The result of the query is given below. This table has four columns. These columns are employee number, Employee name, Department location, and department name.
In the above table, all the rows of the right table which is the department table are there. As department number four is not allotted to any employee but it's in the result this is the rule of SQL RIGHT JOIN.
Example-2: RIGHT JOIN Two Tables
Now, we will discuss another example which is taking student-related tables. To keep it simple we are trying to use simple examples.
Create the table by the following query
Create table stu ( s_id primary key, s_name);
The result of the query is below table which student table. This table has three columns. These columns are student name, student id, and subject.
Create the table by the following query.
Create table marks ( s_id, course_id primary key, sub, marks);
The result of the query is below the marks table. This table has four column subject name, student id, marks and course id. The course id and student id are the foreign keys in this table.
Now we want to join the marks of the student at the student table. For this purpose, we are going to use the RIGHT JOIN. Because the requirement is only the marks.
For the SQL RIGHT JOIN, the query will be following
Select (S_id, sub, mark, s_name from stu Right join mark On ( stu.s_id= mark.s_id));
The resultant table contains four columns. These columns are student id, student name, subject name, and marks. This query results in all the matching rows of both tables plus the rows of the right column which is marks in this case that why it has null values.
To run the query we prefer to use PostgreSQL. The result of the above query after the implementation is given below.
You can see the null values.
Now we are going to apply LEFT JOIN on these to the table to verify whether the result of both cases is the same. In the LEFT JOIN case, we going to keep the marks table on the left side.
It gives the same result as the SQL RIGHT JOIN on gave when the marks table is on the right side. We just change the query from RIGHT JOIN to left. Which is given
Select stu.s_id, marks.sub, marks.marks, stu.s_name from marks left join stu On stu.s_id= marks.s_id;
Now trying another trick. In which going to apply RIGHT JOIN as marks table on the left side and student table on the right side.
The query for the above-mentioned scenario is:
Select stu.s_id, marks.sub, marks.marks, stu.s_name from marks Right join stu On stu.s_id= marks.s_id;
The results of the query in PostgreSQL are shown in the figure below.
Now, in this situation, there is no null value. Because student table is on the right side which joins with the marks, and the subject column of the marks. That is why there is no null entry.
Example-3: RIGHT JOIN Three Tables
In this example, we have three tables
- Student Table.
- Course Table.
- Marks Table.
Before we write the query we should first examine the tables.
The student table has three columns. Which are student id, student name, subject name. student id is the primary key.
The marks table have three column student id, marks and course id. Student id and course id are the foreign keys.
In the course table, there are four columns. Which are course name, course id, enrollement and fee. Course id is the primary key.
In the above scenario, we want to find the student marks in a particular subject. For this we are performing SQL RIGHT JOINs on the 3 tables.
Now going to apply the query. Which is given below:
Select stu.s_id, course.coursename, marks.marks, stu.s_name from stu Right join marks On stu.s_id= marks.s_id Right join course on marks.course_id = course.course_id ;
The result of the table is given below: The resultant table has four columns. Which are student id, course name, marks, and student name.
From the result of the query, we can observe that all the rows of both the right table ‘marks’ and ‘course’ are there in the results.
Example-4: RIGHT JOIN Three Tables
In this example, we want to know which students enroll in which course. For this problem, we have 3 tables. 1)student table 2)course table 3)student course table.
In the student table there are two columns one is student id and the other is student name.
The course table has four columns. Which are course name, course id, enrollmenet, and fee. In this table course id is the primary key.
The student course table has two columns one is course id and the other is student id both are foreign keys there.
After observing the tables we came to know that, the primary key of both the tables student and courses are foreign key in the table. Now, we want to find the course name in which students have enrolled.
Query for the above mentioned scenario is given below.
Select stu.s_id, course.coursename, stu.s_name,course.course_id from stu Right join studentcourse On stu.s_id= studentcourse.s_id Right join course on studentcourse.course_id = course.course_id ;
Results are given below. The resultant table have four column student id, course name, student name and, course id.
There is no null cell in the result table because all the values of the right side tables are student course and course tables. All the rows of both these tables are in the resultant table.
Implementation in PostgreSQL is given below.
Example-5: RIGHT JOIN Two Tables
Now we discuss another example, customer and product relation. As we want to find which customer buy which product. These scenarios are better in the case where we want to know the customer's product preference. Before proceeding we should first observe the table.
Firstly create the product table.
Create a query for that table is:
Create table product ( productname varchar, custid int);
The product table has two columns product name and customer id. Here in this table customer id is the foreign key.
The query for creating the table is below.
Create table customer (custname varchar, custid int primary key)
It has two columns customer name and customer id. The customer id is the primary key.
The query for the above scenario using the SQL RIGHT JOIN is the following.
Select customer.custid, customer.custname,product.productname From customer Right join product On customer.custid = product.cust.id;
The result of the query is given in the below table. Which has three columns. Customer id, customer name, and product name.
SQL join is a complex task but some people find it tricky. The joining statement has the select statement and then at the end, there is a joining portion. The column name in the joint statement should be preceded by the table name. Which we called a qualified column. Joining means to get the matching rows from different tables and generate the resultant table.
The RIGHT JOIN or the outer RIGHT JOIN gives all the matching rows plus to the left table and all the rows in the right table which are not in the left table. It's similar to LEFT JOIN. Matching means both tables have similar values. In case if the right table has no matching value with the left table then it is considered as the null value.
In this blog, we have covered the different examples of the RIGHT JOIN. Like employee relation with the project. Employee relation with the department. The student relation with the marks. The sale relation with the customer and the customer relation with the product. Student relation with the course and many more. The result of these joins depends upon the scenario.