SQL LEFT JOIN

What is LEFT JOIN?

LEFT JOIN, also known as LEFT OUTER JOIN, is a type of join that returns all records from the left table (table1) and the matched records from the right table (table2). If there is no match, NULL values are returned for columns from the right table.

Syntax of LEFT JOIN

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

In this syntax, table1 is the left table, and table2 is the right table. The join is performed based on the specified column_name.

LEFT JOIN Example

Here’s an example of using LEFT JOIN to retrieve data from two related tables:

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

This query selects the CustomerName from the Customers table and the OrderID from the Orders table. If a customer has not placed any orders, the OrderID will be NULL.

Use Cases of LEFT JOIN

LEFT JOIN is useful in various scenarios, such as:

• Retrieving all records from a primary table while showing related data from a secondary table, even if no matches exist.
• Finding records in one table that do not have corresponding entries in another table.
• Generating reports that include all items from a category, including those with no associated transactions.

Practice Exercise

Write your own LEFT JOIN queries using example tables like Customers and Orders. Try to include different columns in your SELECT statement!