Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

What is outer join in SQL?

Khizar Hayat Saani

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

We can use the OUTER JOIN keyword to join two tables in our database. The query returns matching and non-matching rows from both tables.

There are three variations of the OUTER JOIN command:

  1. LEFT OUTER JOIN (or LEFT JOIN)

  2. RIGHT OUTER JOIN (or RIGHT JOIN)

  3. FULL OUTER JOIN (or FULL JOIN)

The LEFT OUTER JOIN keyword

The LEFT OUTER JOIN keyword returns a table that contains all the rows from the left table and matching rows from the right table.

All the rows from the left table are returned, even if a particular row has no match in the right table.

In a non-matching case, the function returns an empty cell or a cell containing null.

The following is the syntax for using the LEFT OUTER JOIN keyword:

SELECT column_name1, column_name2,...
FROM left_table
LEFT OUTER JOIN right_table ON left_table.ID = right_table.ID
Rows returned by LEFT OUTER JOIN

The RIGHT OUTER JOIN keyword

The RIGHT OUTER JOIN keyword returns a table that contains all the rows from the right table and matching rows from the left table.

All the rows from the right are returned, even if a particular row has no match in the left table.

In a non-matching case, the keyword returns an empty cell or a cell containing null.

The following is the syntax for using the RIGHT OUTER JOIN keyword:

SELECT column_name1, column_name2,...
FROM left_table
RIGHT OUTER JOIN right_table ON left_table.ID = right_table.ID
Rows returned by RIGHT OUTER JOIN

The FULL OUTER JOIN keyword

The FULL OUTER JOIN keyword returns a table that contains all the rows from the left table and all the rows from the right table.

All the rows from the left and right tables are returned, even if a particular row has no match in the other table.

In a non-matching case, the keyword returns an empty cell or a cell containing null.

The following is the syntax for using the FULL OUTER JOIN keyword:

SELECT column_name1, column_name2,...
FROM left_table
FULL OUTER JOIN right_table ON left_table.ID = right_table.ID
Rows returned by FULL OUTER JOIN

Code

We begin by creating a demo database, populating it with values, and displaying the current database.

Our Customer_Orders database consists of two tables. The first table, Customers, stores the CustomerID, Name, and Address of the customers. The second table, Orders, stores the OrderID, CustomerID, Order_Date, and the Cost.

CREATE TABLE Customers(
CustomerID int PRIMARY KEY,
Customer_Name varchar(20),
Address varchar(50)
);
CREATE TABLE Orders(
OrderID int PRIMARY KEY,
CustomerID int,
Order_Date date,
Cost int
);
INSERT INTO Customers
VALUES (1, 'Dave', 'LA');
INSERT INTO Customers
VALUES (2, 'Alberto', 'NYC');
INSERT INTO Customers
VALUES (3, 'Joe', 'NJ');
INSERT INTO Orders
VALUES (11, 1, '2021/08/10', 100);
INSERT INTO Orders
VALUES (13, 3, '2020/10/13', 250);
SELECT * FROM Customers;
SELECT * FROM Orders;

Now that we have set up our database, we proceed to use the JOIN statements.

LEFT OUTER JOIN

SELECT * FROM Customers
LEFT OUTER JOIN Orders on Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerID asc

We get the following table as the output:

CustomerID

Customer_Name

Address

OrderID

CustomerID

Order_Date

Cost

1

Dave

LA

11

1

2021-08-10

100

2

Alberto

NYC

NULL

NULL

NULL

NULL

3

Joe

NJ

13

3

2020-10-13

250

We apply LEFT OUTER JOIN with Customers as our left table and Orders as our right table.

Since Alberto placed no order, the row with his entry contains NULL in the fields that did not match from the Orders table.

RIGHT OUTER JOIN

SELECT * FROM Orders
RIGHT OUTER JOIN Customers on Orders.CustomerID = Customers.CustomerID
ORDER BY Orders.CustomerID asc

We get the following table as the output:

OrderID

CustomerID

Order_Date

Cost

CustomerID

Customer_Name

Address

NULL

NULL

NULL

NULL

2

Alberto

NYC

11

1

2021-08-10

100

1

Dave

LA

13

3

2020-10-13

250

3

Joe

NJ

We apply RIGHT OUTER JOIN with Orders as our left table and Customers as our right table.

The use of RIGHT OUTER JOIN ensures that all the rows from the Customers table are included.

Since Alberto placed no order, the row with his entry contains NULL in the fields that did not match from the Orders table.

FULL OUTER JOIN

SELECT * FROM Orders 
FULL OUTER JOIN Customers on Orders.CustomerID = Customers.CustomerID
ORDER BY Orders.OrderID asc

We get the following table as the output:

OrderID

CustomerID

Order_Date

Cost

CustomerID

Customer_Name

Address

NULL

NULL

NULL

NULL

2

Alberto

NYC

11

1

2021-08-10

100

1

Dave

LA

13

3

2020-10-13

250

3

Joe

NJ

As we apply FULL OUTER JOIN, the output table contains all rows from both tables. The non-matching values are replaced by NULL.

RELATED TAGS

sql

CONTRIBUTOR

Khizar Hayat Saani
Copyright ©2022 Educative, Inc. All rights reserved

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring