Difference Between Left Join and Right Join

Database management system or DBMS is the application software that allows us with its feature to store and manage the data which is collected from different types of sources. Now these sources can be from social media, user feedback, real-time generated data, etc.

Database Management System follows the standardized model, which is a relational model and it uses the SQL language which is a Structured Query Language through which the programmers or developers can write the custom query and retrieve or store the data in the database in the form of tables. There can be some scenarios where we need to return the result by combining the two tables.

So for that purpose, joins come into the picture. Joins in the database are used to combine two tables. So in this article, we will go through the detailed information about two types of important joins in DBMS, that is Left Join and Right Join.

Left Join

Left Join in SQL language is used to retrieve all the data or records from the left table and the matching data or records from the right table. In some cases, if there is no match of data or the record as per the given query by the programmer, then the left join will still display or return the rows from the left table and will show the NULL values for the columns of the right table.

In the context of the query, below is the syntax of the Left Join.

Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table ON
join_condition;

Example

Employee_Data Table

emp_id	emp_name	emp_dept
1	Gaurav	HR
2	Anjali	IT
3	Akshada	Finance
4	Amit	IT

Department_Data Table

department_name	location_name
HR	Building 1
IT	Building 2
Marketing/Sales	Building 3

Query for Left Join

SELECT Employee_Data.emp_name, Employee_Data.emp_dept, Department_Data.location_name
FROM Employee_Data
LEFT JOIN Department_Data ON Employee_Data.emp_dept = Department_Data.department_name

Result

emp_name	emp_dept	location_name
Gaurav	HR	Building 1
Anjali	IT	Building 2
Akshada	Finance	NULL
Amit	IT	Building 2

Explanation

In the above results, we can see that data or the records from the (Employee_Data) table are included, and all the matching records from the (Department_Data) table are also included. One f the record has no match for the department_name as Finance, so its corresponding location_name is filled with the NULL value.

Right Join

Right Join in SQL is used to retrieve all the data or records that are stored from the rightmost table and the matching records from the leftmost table. In some cases, there might be a situation where there are no matches of the data, then in this case Roght Join will still include the rows from the right table but it will display the NULL values for the columns that are associated with the left table.

In the context of the query, below is the syntax of the Right Join.

Syntax

SELECT columns 
FROM left_table 
RIGHT JOIN right_table ON 
join_condition;

Now, let’s see the example of Right Join:

Example:

Let’s consider the same tables used in the above Left Join Example:

Query for Right Join

SELECT Employee_Data.emp_name, Employee_Data.emp_dept, Department_Data.location_name
FROM Employee_Data
RIGHT JOIN Department_Data ON Employee_Data.empt_dept = Department_Data.department_name

Result

emp_name	emp_dept	location_name
Gaurav	HR	Building 1
Anjali	IT	Building 2
NULL	Marketing/Sales	Building 3
Amit	IT	Building 2

Explanation

In the above results, we can see that all the records or the data from the (Department_Data) table are been displayed and all the matching records from the (Employee_Data) table are also displayed. There is no match for the department Marketing in the Employee_Data table, so the corresponding emp_name is filled with the NULL value and the emp_dept has its original value which is Marketing/Sales.

Left Join v/s Right Join

Parameter	Left Join	Right Join
Definition	Left Join retrieves all the records and the data from the left table and all matching records from the right table.	Right Join retrieves all the records and the data from the right table and all matching records from the left table.
Join Keyword Use	Here, the “LEFT JOIN” keyword is used.	Here, the “RIGHT JOIN” keyword is used.
Table Precedence	In Left Join, the left table is given higher precedence.	In Right Join, the right table is given higher precedence.
Matching Rows	In Left Join it returns rows from both tables and all the rows from the left table.	In Right Join it returns rows from both tables and all rows from the right table.
Usage	Left Join is more used as compared to Right Join.	Right Join is less used as compared to Left Join.
Syntax	SELECT columns FROM left_table LEFT JOIN right_table ON join_condition;	SELECT columns FROM left_table RIGHT JOIN right_table ON join_condition;

FAQs: Right Join vs Left Join

1. What happens to the non-matching rows when we use Right Join?

In Right Join, all the non-matching rows from the left-most table are included in the output result and the values are displayed as NULL.

2. Can we use Left Join and Right Join in the same query?

Yes, we can use Left Join and Right Join in the same query. We can also use Inner Join with these 2 Joins. This is referred to as the “Full Outer Join” in DBMS.

3. Whether it is allowed to use LeftJoin and Right Join with more than 2 tables in a database?

Yes, In the database, we can use Left Join and Right Join with more than 2 tables, we need to give the proper join condition in the query.

4. How do the duplicate records been handled by Left Join and Right Join?

Left Join and Right Join handles the duplicate records in the table as a single or individual row and then it includes them in the result or the output.