LEFT JOIN

Introduction

In SQL, a LEFT JOIN is used to combine rows from two or more tables based on a related column between them. The key characteristic of a LEFT JOIN is that it returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the side of the right table.

This type of join is particularly useful when you want to ensure that you get all entries from one table (the "left" table) regardless of whether they have corresponding entries in another table (the "right" table).

Concept

A LEFT JOIN can be visualized as a scenario where you are looking at a list of customers and their orders. You want to see every customer, even if they haven't placed any orders yet.

The syntax for a LEFT JOIN is as follows:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

In this query:

• table1 is the left table.
• table2 is the right table.
• The ON clause specifies the condition for matching rows between the two tables.

Examples

Example 1: Basic LEFT JOIN

Let's consider two tables, Customers and Orders.

Customers Table

Orders Table

Now, let's perform a LEFT JOIN to get all customers and their orders:

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Output

In this example, you can see that all customers are listed. Alice and Bob have orders associated with them, but Charlie does not.

Example 2: Handling NULLs

Let's modify the previous query to include a condition where we filter out customers who do not have any orders:

SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NOT NULL;

Output

This query will only return customers who have placed orders.

Example 3: Multiple Tables

You can also perform a LEFT JOIN with multiple tables. For instance, let's add another table called OrderDetails.

OrderDetails Table

Now, let's perform a LEFT JOIN with three tables:

SELECT Customers.CustomerName, Orders.OrderID, OrderDetails.ProductName, OrderDetails.Quantity
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;

Output

In this example, you can see that all customers are listed, and their corresponding orders and order details are shown. If a customer does not have any orders or order details, the result is NULL.

What's Next?

After mastering LEFT JOIN, you might want to explore other types of joins such as RIGHT JOIN. A RIGHT JOIN returns all records from the right table and matched records from the left table. This can be useful in scenarios where you want to ensure that you get all entries from a specific table.

Stay tuned for more tutorials on SQL and data relationships!