SQL RIGHT JOIN Explained with Examples


Written by - Deepak Prasad

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.

SQL RIGHT JOIN Explained with Examples
SQL RIGHT JOIN VENN DIAGRAM

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

Table#1: emp
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.
SQL RIGHT JOIN Explained with Examples

 

Table#2: dept
The department table has three columns. These columns are department number, department location, department name. Here department number is the primary key.
SQL RIGHT JOIN Explained with Examples

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.
SQL RIGHT JOIN Explained with Examples

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.

Table student:
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.
SQL RIGHT JOIN Explained with Examples

Marks table:
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.
SQL RIGHT JOIN Explained with Examples

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.
SQL RIGHT JOIN Explained with Examples

To run the query we prefer to use PostgreSQL. The result of the above query after the implementation is given below.

SQL RIGHT JOIN Explained with Examples

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;

SQL RIGHT JOIN Explained with Examples

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.

SQL RIGHT JOIN Explained with Examples

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.

Student Table:
The student table has three columns. Which are student id, student name, subject name. student id is the primary key.
SQL RIGHT JOIN Explained with Examples

Marks table:
The marks table have three column student id, marks and course id. Student id and course id are the foreign keys.
SQL RIGHT JOIN Explained with Examples

Course Table:
In the course table, there are four columns. Which are course name, course id, enrollement and fee. Course id is the primary key.
SQL RIGHT JOIN Explained with Examples

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.

SQL RIGHT JOIN Explained with Examples

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.
SQL RIGHT JOIN Explained with Examples

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.

SQL RIGHT JOIN Explained with Examples

 

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.

Student table:
In the student table there are two columns one is student id and the other is student name.
SQL RIGHT JOIN Explained with Examples

Course table:
The course table has four columns. Which are course name, course id, enrollmenet, and fee. In this table course id is the primary key.
SQL RIGHT JOIN Explained with Examples

Student course:
The student course table has two columns one is course id and the other is student id both are foreign keys there.
SQL RIGHT JOIN Explained with Examples

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.
SQL RIGHT JOIN Explained with Examples

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.

SQL RIGHT JOIN Explained with Examples

 

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.
SQL RIGHT JOIN Explained with Examples

Customer table:
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.
SQL RIGHT JOIN Explained with Examples

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 RIGHT JOIN Explained with Examples

 

Summary

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.

Deepak Prasad

He is the founder of GoLinuxCloud and brings over a decade of expertise in Linux, Python, Go, Laravel, DevOps, Kubernetes, Git, Shell scripting, OpenShift, AWS, Networking, and Security. With extensive experience, he excels in various domains, from development to DevOps, Networking, and Security, ensuring robust and efficient solutions for diverse projects. You can reach out to him on his LinkedIn profile or join on Facebook page.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X