LEFT Join

A LEFT JOIN, also known as a LEFT OUTER JOIN, is a type of join in SQL that returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table for a row in the left table, the result will still include the row from the left table, with NULL values for columns from the right table.

The basic syntax of the LEFT JOIN is as follows:

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

In this syntax, table1 is the left table, table2 is the right table, and column_name is the column in both tables that you use as the join condition. The ON keyword is used to specify the join condition, which indicates the relationship between the tables.

Here's an example to illustrate the LEFT JOIN:

Suppose we have two tables, "Customers" and "Orders," with columns as follows:

Customers table:

CustomerID
CustomerName

Orders table:

OrderID
CustomerID
OrderDate
TotalAmount

If we want to retrieve all customer names along with order details for each order, including customers who have not placed any orders, we can use the LEFT JOIN on the CustomerID column:

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

In this example, the LEFT JOIN combines all rows from the "Customers" table and the matching rows from the "Orders" table where the CustomerID values match. For the unmatched rows where there is no corresponding order for a customer, the columns from the "Orders" table will contain NULL values.

The result set will include all customer names along with order details for each order placed by a customer. If a customer has not placed any orders, their details will still be included in the result set, but the order details will have NULL values.

LEFT JOIN is commonly used when you want to retrieve all rows from one table and only matching rows from another table, without excluding any rows from the left table. It is especially useful when you want to analyze relationships between tables and include all data from one table, even if there are no corresponding matches in the related table.