Left Join

A LEFT JOIN

returns all the records from the left table and the matching records from the right table. If there are no matches in the right table, the result will contain NULL values for columns from the right table.

Task

Retrieve a list of all customers along with any orders they have placed. If a customer hasn’t placed any orders, still display their name with NULL values for the order details. Additionally, include payments for the orders wherever applicable. This will allow us to see: All customers, even those without orders. All orders placed by customers, with payments (if any) listed. NULL values for orders or payments where no matching records exist.

Tables: Customers, Orders, and Payments

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie
4	David

OrderID	CustomerID	Amount
101	1	150
102	2	200
103	1	350
104	3	400

PaymentID	CustomerID	Amount
201	101	150
202	102	200
203	103	350

How Relationships Work in This Example

In this example, we have three related tables: Customers, Orders, and Payments. The relationships between these tables illustrate how data is connected and what happens when some records don't have matching entries. Customers Table (Left Table):

This table contains all the customers, each identified by a unique CustomerID.
A LEFT JOIN ensures that all customers will appear in the result, even if they haven’t placed any orders.
Example: David is a customer who hasn’t placed any orders, so his OrderID and PaymentAmount will be NULL in the result.

Orders Table (Right Table for First Join):

Some customers may have multiple orders, which will result in multiple rows in the result for the same customer.
Example: Alice placed two orders, so both of her orders will appear on separate rows.

Payments Table (Right Table for Second Join):

This table contains payments made for specific orders, linked through the OrderID.
Not all orders have payments, so some rows will display NULL for payment amounts.
Example: Charlie’s order (OrderID 104) has no payment, so the PaymentAmount will be NULL.

Payments Table (Right Table for Second Join):

This table contains payments made for specific orders, linked through the OrderID.
Not all orders have payments, so some rows will display NULL for payment amounts.
Example: Charlie’s order (OrderID 104) has no payment, so the PaymentAmount will be NULL.

SQL Query for Left Join:

         
    SELECT
        Customers.CustomerName,
        Orders.OrderID,
        Orders.Amount AS OrderAmount,
        Payments.Amount AS PaymentAmount
    FROM
        Customers
    LEFT JOIN 
    Orders ON Customers.CustomerID = Orders.CustomerID;
    LEFT JOIN 
    Payments ON Orders.OrderID = Payments.OrderID;

Why This Query is Useful

Tracks customers with or without orders, ensuring you don't miss any customer data, even if no orders were placed.
Identifies unpaid orders by showing NULL in the payment column, helping detect missing payments.
Provides a complete view of customer transactions, combining customer, order, and payment data in one result for better analysis and reporting.

Result Table

Query Output: Customer Orders with Payments

CustomerName	OrderID	OrderAmount	PaymentAmount
Alice	101	150	150
Alice	103	350	350
Bob	102	200	200
Charlie	104	400	NULL
David	NULL	NULL	NULL

Why Are There NULLS for David?

David hasn’t placed any orders, so no matching rows exist for him in the Orders table.
A LEFT JOIN keeps all rows from the Customers table, even if there are no matches in the joined table.
Since no related orders or payments exist for David, the OrderID, OrderAmount, and PaymentAmount columns are filled with NULL values.

Explanation

The query output demonstrates the behavior of a LEFT OUTER JOIN by listing all customers, even those without orders, along with their corresponding order and payment details, if available. For example, Alice appears twice because she placed two orders, each with a matching payment, while Bob and Charlie show up once with their respective orders — although Charlie’s payment is NULL since no payment was made. David, who has not placed any orders, still appears in the result with NULL values for both the order and payment columns. This output highlights how a LEFT JOIN retains all rows from the Customers table (left table), filling in NULL values where there are no matches in the Orders or Payments tables.

When to Use Left Join

A LEFT JOIN is ideal when you want to ensure that all rows from the left table are included in the result, even if there are no matching rows in the right table. This join is especially useful when one table may have incomplete or missing data and you still need to retain all the primary information from the left table. It’s commonly used in scenarios where you want to list all entities (e.g., customers or products) , even if they don’t have related records (such as orders or transactions), ensuring that no data from the primary table is excluded.

Conclusion

A LEFT JOIN ensures that all rows from the left table are included, even if there are no matching records in the right table, with unmatched values appearing as NULL. This makes it especially useful for maintaining complete data from a primary table while enriching it with related information from other tables, where some relationships may be missing.