Left Outer Join And Left Join

When working with databases, it is essential to know different types of joins in SQL. Joins are used to combine data from multiple tables based on a given condition. Among those joins, the left outer join and left join are the most commonly used.

Both joins are used to combine data from two or more tables but have significant differences in their output. In this article, we will discuss the differences between the left outer join and left join.

Let’s start with the left join.

Left Join:

The left join, also known as the left outer join, returns all the records from the left table and matching records from the right table. If there are no matching records found in the right table, then NULL values will be returned.

Let’s consider two tables named “customers” and “orders.” The “customers” table contains the customer’s details like customer ID, name, address, and contact number, while the “orders” table contains the order details like order ID, order date, amount, and the customer who placed the order.

The following query shows how to use left join to fetch all customers and their orders, even if some customers never placed any orders.

SELECT customers.name, orders.order_id, orders.order_date, orders.amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

The output will include all customer records regardless of whether they have placed any orders.

Customer Name| Order ID | Order date | Amount
————|———|————|——-
John | 102 | 2021-03-20 | $275
John | 107 | 2021-05-01 | $150
Mark | NULL | NULL | NULL
Peter | 103 | 2021-04-11 | $180
Peter | 105 | 2021-04-22 | $90
Alex | 101 | 2021-03-10 | $200

In the above result set, Mark is listed as null because he hasn’t placed any orders yet, and John, Peter, and Alex ordered at least one order. Also, the columns from the “orders” table show null values for Mark since he doesn’t have a matching record in the “orders” table.

Left Outer Join:

The left outer join, also known as the left join, returns all the records from the left table and matching records from the right table. If there are no matching records found in the right table, then the left outer join returns NULL values.

Using the same “customers” and “orders” tables as discussed earlier, let’s look at the query that uses left outer join to fetch the customer’s details and their orders.

SELECT customers.name, orders.order_id, orders.order_date, orders.amount
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

The left outer join returns exactly the same result set as the left join.

Conclusion:

In summary, both left join and left outer join are used to combine records from multiple tables. The main difference between them is that a left outer join returns all records from the left table and NULL for records in the right table that do not match the condition. Whereas, a left join returns all records from the left table and matching records from the right table.

Now that you understand the difference between the left and left outer join, you can choose the type of join that best suits your needs. However, It is advisable to use the left outer join to ensure that all records from the left table are included in the result set, even if there are no matching records found in the right table.

Keywords: left outer join, left join, SQL, join, database, tables, records, NULL values, matching records, result set, condition.