When working with relational databases, there are times when you want to retrieve all rows from one table, even if there are no matching records in another table. This is where the LEFT JOIN comes in. Today, we’ll explore how LEFT JOIN works using an example dataset and explain its importance in SQL.

What is a LEFT JOIN?

A LEFT JOIN, also known as a LEFT OUTER JOIN, retrieves all rows from the "left" table (the first table in the query) and only the matching rows from the "right" table (the second table). If there is no match, the result includes NULL values for the columns from the right table.

Syntax of LEFT JOIN

sqlcode

SELECT column_name(s)

FROM table1

LEFT JOIN table2 ON table1.common_column = table2.common_column;

• table1 (Left Table): The table from which all rows will be included.
• table2 (Right Table): The table that contributes matching rows (if any).
• common_column: The column used to establish a relationship between the tables.

Dataset

Let’s consider the following Customers and Orders tables:

Customers Table

Orders Table

Scenario

We want to find all customers, along with their orders. Even if a customer hasn’t placed an order, they should still appear in the results.

SQL Query

Here’s how we can achieve this using a LEFT JOIN:

sql code

SELECT Customers.CustomerID, Customers.CustomerName, Customers.Country, Orders.OrderID, Orders.OrderAmount

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result

Explanation

• Customer Alice has an order (OrderID = 101, OrderAmount = 250), so her details are included with her order.
• Customer Bob has an order (OrderID = 102, OrderAmount = 450), so his details are also included.
• Customer Charlie does not have a matching record in the Orders table, so the OrderID and OrderAmount columns return NULL.

Key Points About LEFT JOIN

1. All Rows from the Left Table: Every row from the left table (Customers) is included, even if there is no match in the right table.
2. NULL for Missing Matches: If no matching row exists in the right table (Orders), the columns from the right table are filled with NULL.
3. Use Cases:
   • Find entities that don’t have related data (e.g., customers without orders, employees without assignments).
   • Include all data from a primary table while enriching it with optional related data.

Conclusion

The LEFT JOIN is an essential SQL tool for combining data while ensuring that all rows from the primary table are included in the results. It is particularly useful for identifying gaps or incomplete relationships between tables. By mastering LEFT JOIN, you’ll be able to perform more comprehensive and insightful analyses in your data projects.