Difference Between Left Join and Left Outer Join

Database in any computer system is the collection of structured or unstructured data that can be used to perform various options like creation, deletion, etc. This database is managed by a special language known as SQL. In SQL language there are different joins that are used to assemble rows from two or more tables from the related column. Some of the joins are Inner Join, Left Join, and Right Join. In this article, we will explore the concepts with examples of Left Join and Left Outer Join. Along with this, we will also go through their main differences.

Left Join

Left Join in SQL language is used to return all the data or records from the left table and the matching data or records from the right table. In the scenario, where there is no match, then the join still consists of the rows from the left table and displays 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;

Now, let’s understand the Left Join through a simple example:

Example:

1. Customer_Data Table:

customer_id	customer_name
1	Gaurav
2	Anjali
3	Ramesh

2. Orders_Data Table:

order_id	customer_id	order_date
1	1	2023-01-23
2	1	2023-02-03
3	3	2023-03-05
4	4	2023-04-10

Query for Left Join

SELECT Customer_Data.customer_id, Customer_Data.customer_name,
Orders_Data.order_id, Orders_Data.order_date
FROM Customers_Data
LEFT JOIN Orders ON
Customers_Data.customer_id = Orders_Data.customer_id;

Result/Output

customer_id	customer_name	order_id	order_date
1	Gaurav	1	2023-01-23
1	Gaurav	2	2023-02-03
2	Anjali	NULL	NULL
3	Ramesh	3	2023-03-05

Explanation

In the above example. Left Join includes all rows from the left table (Customer_Data) and matched them with the corresponding rows of the right table (Orders_Data). Here, Customer Gaurav has 2 orders, Anjali has no orders (NULL), and Ramesh has 1 order.

Left Outer Join

The concept of Left Outer Join is similar and same to the Left Join, and both these terms are interchangeably used. The keyword used here is “Outer“, which is optional and also doesn’t impact the result.

Let’s see the syntax of Left Outer Join:

Syntax

SELECT columns

FROM left_table

LEFT OUTER JOIN right_table ON

join_condition;

Example:

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

Query for Left Outer Join

SELECT Customer_Data.customer_id, Customer_Data.customer_name,
Orders_Data.order_id, Orders_Data.order_date FROM Customers_Data 
LEFT OUTER JOIN Orders ON 
Customers_Data.customer_id = Orders_Data.customer_id;

Result/Output

customer_id	customer_name	order_id	order_date
1	Gaurav	1	2023-01-23
1	Gaurav	2	2023-02-03
2	Anjali	NULL	NULL
3	Ramesh	3	2023-03-05
4	NULL	NULL	NULL

Explanation

In the above example, non-matching records from the right table (“Orders_Data“) are included, and NULL values are shown for the right table columns. Thus, the customer with ‘customer_id‘ 4 in the Orders_Data table, which doesn’t have a matching record in the Customer_Data table is also included in the result set and the NULL values are shown, which was not displayed in the Left Join condition.

Difference Between Left Join and Left Outer Join

Parameter	Left Join	Outer Join
Matching Records	In Left Join, matching records from the right table are included.	In Left Outer Join, matching records from the right tables are included.
Non-Matching Records	In Left Join, non-matching records from the rightmost table are excluded.	In Left Outer Join, non-matching records from the right table are included and the NULL value is displayed for the right table columns.
Join Keyword	LEFT JOIN	LEFT OUTER JOIN
Null Values	No NULL values are shown for the right table columns.	NULL values are shown for the right table columns in case there is no match.
Syntax	SELECT columns FROM left_table LEFT JOIN right_table ON join_condition;	SELECT columns FROM left_table LEFT OUTER JOIN right_table ON join_condition;

FAQs on Left Join and Left Outer Join

1. When should we use a Left Join?

Left Join should be used when we need to return all rows from the left-most table, even if there are no matching rows in the right-most table. This is useful in the scenario when we want to list all the data from the left table, regardless of whether it contains data in the right table.

2. What are the benefits of using Left Join?

There are various types of benefits of using Left Join, it potentially allows us to display all the data in the left table, even though it does not have any data in the right table. This can be used for auditing tasks.

3. Explain the results of Left Outer Join.

The results of Left Outer Join consist of rows from the left table, and also the matching rows from the right table. In case there are no matching rows from the right table, the corresponding columns in the result set will be shown as NULL.

4. Can we use the ORDER BY clause along with Left Outer Join?

Yes, we can use the ORDER BY clause along with Left Outer Join in the query. This will order the results of the join like it would with any other query.