What is outer join in SQL?

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

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

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

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.

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:

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:

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:

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