sql_join_left

SQL LEFT JOIN Clause

The SQL LEFT JOIN Clause

The LEFT JOIN clause (also written as LEFT OUTER JOIN) is used to combine rows from two tables based on a related column between them. It returns all rows from the left table (table1) and the matched rows from the right table (table2).

If there is no match for a row in the left table, the result will still include that row, but with NULL values in the columns from the right table.

SQL LEFT JOIN Syntax

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

The OUTER keyword in LEFT OUTER JOIN is optional in most SQL database systems (e.g., MySQL, PostgreSQL, SQL Server, Oracle) and typically omitted for brevity.

Conceptually, a LEFT JOIN works as follows:

1. It starts with all rows from the table1 (the ‘left’ table specified before LEFT JOIN).

2. For each row from table1, it tries to find matching rows in table2 (the ‘right’ table) based on the condition specified in the ON clause.

3. If matching rows are found in table2, their columns are combined with the table1 row.

4. If no matching rows are found in table2 for a table1 row, that table1 row is still included in the result, but the columns selected from table2 will have NULL values.

Demo Database Context

For this example, we’ll use two conceptual tables:

• Customers: Contains customer information, with columns like CustomerID (Primary Key) and CustomerName.

• Orders: Contains order information, with columns like OrderID (Primary Key), CustomerID (Foreign Key referencing Customers), and OrderDate.

We want to list all customers and any orders they might have placed.

SQL LEFT JOIN Example

The following SQL statement will select all customers and list their corresponding orders. If a customer has no orders, they will still be listed, but their order-related fields will be NULL.

SELECT C.CustomerName, O.OrderID, O.OrderDate
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName, O.OrderDate;

Key points about the result:

• All customers from the Customers table will be included at least once.

• If a customer has placed multiple orders, they will appear multiple times in the result, once for each order.

• If a customer has not placed any orders, their CustomerName will be listed, but O.OrderID and O.OrderDate will be NULL for that row.

Practical Use Case:

LEFT JOIN is very useful when you need to retrieve all records from one primary entity (e.g., all customers, all products) and then optionally show related information from another table, even if that related information doesn’t exist for every record in the primary entity.

For instance, finding customers who have never placed an order:

SELECT C.CustomerName
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.OrderID IS NULL;