Difference Between Left Join and Left Outer Join

Last Updated : 18 Sep, 2024

In SQL language, different joins are used to assemble rows from two or more tables from the related column. The terms "Left Join" and "Left Outer Join" are used interchangeably in SQL but they refer to the same concept. A Left Join retrieves all records from the left table (the first table in the query) and matches them with records from the right table (the second table) based on a specified condition.

In this article, we will explore the concepts with examples of Left Join and Left Outer Join. along with their examples and differences between them.

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

Suppose We need to retrieve a list of all customers along with their corresponding order details, including customers who have not placed any orders.

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;

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.

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

Let's retrieve a list of all customers along with their order details, if any. Even if a customer has not placed an order, their information should still appear in the result.

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;

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.