Difference Between Left Join and Left Outer Join

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:

2. Orders_Data Table:

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:

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

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

Conclusion

In conclusion, the Left Join retrieves all records from the left table and matches them with corresponding records from the right table, filling in NULL values where there are no matches. While "Left Outer Join" is the technically accurate term, "Left Join" is a more common shorthand. Understanding that these terms are synonymous helps prevent confusion and ensures effective use of joins when combining data from multiple tables in SQL queries.