SQL LEFT JOIN
SQL LEFT JOIN is a type of join operation used to combine data from two or more tables based on the values of the columns they have in common. Unlike INNER JOIN, LEFT JOIN returns all the rows from the left table (the first table in the query), and only the matching rows from the right table. If there are no matching rows in the right table, the result will still contain all the rows from the left table, with NULL values in the columns from the right table.
Let's consider two tables, 'Customers' and 'Orders' for the purpose of this example:
Customers Table:
| CustomerID | CustomerName | ContactName | Country |
|---|---|---|---|
| 1 | Alfreds | Maria | Germany |
| 2 | Ana Trujillo | Ana | Mexico |
| 3 | Antonio | Antonio | Spain |
Orders Table:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 1 | 3 | 2022-12-01 |
| 2 | 1 | 2022-12-02 |
To join these two tables using LEFT JOIN, we would write the following SQL query:
1SELECT Customers.CustomerName, Orders.OrderDate 2FROM Customers 3LEFT JOIN Orders 4ON Customers.CustomerID = Orders.CustomerID;
This query will return the following result:
| CustomerName | OrderDate |
|---|---|
| Alfreds | 2022-12-02 |
| Ana Trujillo | NULL |
| Antonio | 2022-12-01 |
The query combines the rows from the two tables where the value of the CustomerID column in the 'Customers' table matches the value of the CustomerID column in the 'Orders' table. However, it returns all the rows from the 'Customers' table, regardless of whether there are matching rows in the 'Orders' table. For the 'Ana Trujillo' customer, there is no matching order in the 'Orders' table, so the result for the OrderDate column is NULL.