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:
LEFT OUTER JOIN (or LEFT JOIN)
RIGHT OUTER JOIN (or RIGHT JOIN)
FULL OUTER JOIN (or FULL JOIN)
LEFT OUTER JOIN keywordThe 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
RIGHT OUTER JOIN keywordThe 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
FULL OUTER JOIN keywordThe 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
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 CustomersVALUES (1, 'Dave', 'LA');INSERT INTO CustomersVALUES (2, 'Alberto', 'NYC');INSERT INTO CustomersVALUES (3, 'Joe', 'NJ');INSERT INTO OrdersVALUES (11, 1, '2021/08/10', 100);INSERT INTO OrdersVALUES (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 JOINSELECT * 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 JOINSELECT * 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 JOINSELECT * 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.
Unlock your potential: SQL joins series, all in one place!
To continue your exploration of SQL joins, check out our series of Answers below:
Different types of SQL joins
Understand the different types of SQL joins used for combining data from multiple tables.
What is inner join in SQL?
Learn how inner joins return matched rows from both tables in SQL.
What is outer join in SQL?
Explore outer joins and how they return unmatched rows as well.
What is a self join in SQL?
Discover how self joins allow a table to join with itself.
What is a natural join in SQL?
Learn how natural joins match columns with the same name in SQL tables.
What is a hash join in SQL?
Understand hash joins, which are used for large-scale data matching and combining.
What is the SQL CROSS JOIN?
Explore the SQL CROSS JOIN to combine every row from one table with every row from another.
What is an equi join in SQL?
Learn how equi joins match rows based on the equality of values between tables.
How to join 3 or more tables in SQL
Master the technique of joining multiple tables in a SQL query for complex data retrieval.
Free Resources