What is the difference between left join and right join?

Overview

A JOIN clause integrates rows (tuples) from two or more tables based on a similar column/attribute between them. There are various varieties of join. Now, we will discuss only two of them, which are LEFT OUTER JOIN and RIGHT OUTER JOIN.

LEFT JOIN

This is sometimes referred to as a Left Outer Join. As a result, Outer is an optional keyword to use with Left Join. The Left Join clause connects two or more tables and retrieves all rows from the left table, as well as those who get matched with the right table, or it returns null if no matching record is found.

RIGHT JOIN

A Right Outer Join is another name for this. As a result, Outer is a term that may be used with Right Join. The Right Join clause joins two or more tables and obtains all rows from the right table and also those who get matched from the left table, returning null if no matching record is found.

Venn diagram

A Venn diagram will help us to understand the concepts of LEFT OUTER JOIN and RIGHT OUTER JOIN more clearly.

LEFT JOIN vs RIGHT JOIN

Example

In this example, we see the difference between LEFT OUTER JOIN and RIGHT OUTER JOIN.

-- Create Country table
CREATE TABLE Country
(
    Country_id int primary key,
    Country_name varchar(15),
    Country_currency varchar(5)
);
-- Insert values
INSERT INTO Country VALUES(1, "India", "INR");
INSERT INTO Country VALUES(2, "Iran", "IRR");
INSERT INTO Country VALUES(3, "Italy", "EUR");
INSERT INTO Country VALUES(5, "Turkey", "TRY");
-- Print Data
SELECT "Country","=>>",country_id AS ID, country_name AS Name,"",
country_currency AS Currency FROM Country; SELECT "";
-- Create City table
CREATE TABLE City
(
    City_id int primary key,
    City_name varchar(15),
    Countryid int
);
-- Insert values
INSERT INTO City VALUES(1, "Mumbai", 1);
INSERT INTO City VALUES(2, "Ankara", 5);
INSERT INTO City VALUES(3, "GOA", "1");
INSERT INTO City VALUES(4, "Tehran", 2);
INSERT INTO City VALUES(5, "Kabul", NULL);
-- Print Data
SELECT "City","=>>",City_id AS ID, City_name AS Name,"",
Countryid FROM City; SELECT "";
-- LEFT Join
SELECT "LEFT OUTER JOIN","=>>",Country_name AS Country,"",
City_name AS City,"",Country_currency AS Currency
FROM Country LEFT JOIN City ON Country.Country_id=City.Countryid;
SELECT "";
-- RIGHT Join
SELECT "RIGHT OUTER JOIN =>>",Country_name AS Country,"",
City_name AS City,"",
Country_currency AS Currency
FROM Country RIGHT JOIN City ON Country.Country_id=City.Countryid;
SELECT "";
Left join vs Right join

Explanation

  • In lines 2-32, we create the Country and City tables using the CREATE command, insert values using the INSERT command, and display the data of the Country table using the SELECT Command.
  • In lines 35-38, we integrate tables using LEFT JOIN. It retrieves all rows from the left table (Country) as well as those that get matched with the right table (City), or it returns null if no matching record is found. Italy has no City in the record, so it shows NULL in the City attribute.
  • In lines 41-45, we join tables using RIGHT JOIN. It returns null if no matching record is discovered, and it obtains all rows from the right table (City) as well as those that match with the left table (Country). Country and currency are also not specified for Kabul; therefore, it shows NULL in both columns.
Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved