LEFT JOIN in SQL

LEFT JOIN in SQL

1. Overview The LEFT JOIN is a type of JOIN operation in SQL that retrieves records from the left table (referred to as the "left" or "first" table) and matching records from the right table (referred to as the "right" or "second" table). It returns all records from the left table and the matching records from the right table. If there are no matches, NULL values are returned for the right table columns.

2. Syntax The syntax for a LEFT JOIN in SQL is as follows:

sql
SELECT column_list
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

3. Example Consider two tables, "Customers" and "Orders", with the following structures and data:

Customers table:

CustomerID	CustomerName
1	John
2	Mary
3	David

Orders table:

OrderID	CustomerID	OrderDate
1	1	2022-05-10
2	2	2022-06-15
3	3	2022-07-20

To retrieve all customers and their corresponding orders (if any), you can use a LEFT JOIN as follows:

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

4. Explanation In the above example, the LEFT JOIN is performed between the "Customers" and "Orders" tables based on the common column "CustomerID". The result includes all records from the left table (Customers) and matching records from the right table (Orders).

The resulting output will be:

CustomerName	OrderID	OrderDate
John	1	2022-05-10
Mary	2	2022-06-15
David	3	2022-07-20

Notice that all customers are included in the result, even if they don't have any corresponding orders. In such cases, the columns related to the right table (Orders) will contain NULL values.

5. Conclusion The LEFT JOIN in SQL allows you to retrieve all records from the left table and the matching records from the right table. It is useful when you want to include all records from the left table, regardless of whether there are matches in the right table.