In SQL, the LEFT JOIN is a type of join that returns all rows from the left table and the matching rows from the right table. If there is no matching row in the right table, NULL values are returned.
Syntax
The syntax for a LEFT JOIN in SQL is as follows:
SELECT column_names
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example
Consider two tables, customers and orders, with the following structure and data:
Customers table:
| customer_id | customer_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Sarah Johnson |
Orders table:
| order_id | customer_id | order_name |
|---|---|---|
| 1 | 1 | Order 1 |
| 2 | 3 | Order 2 |
| 3 | 2 | Order 3 |
To retrieve all customers and their corresponding orders using a LEFT JOIN, you can use the following query:
SELECT customers.customer_name, orders.order_name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
The result of the above query will be:
| customer_name | order_name |
|---|---|
| John Doe | Order 1 |
| Jane Smith | Order 3 |
| Sarah Johnson | Order 2 |
Notice that all customers are included in the result set, even if they do not have any corresponding orders. For example, John Doe has an order (Order 1), but Jane Smith and Sarah Johnson also appear in the result set even though they have no orders. In such cases, the order_name column contains a NULL value.
Conclusion
The LEFT JOIN operation allows you to retrieve data from multiple tables, including rows that do not have matching values in the joined table. It is a powerful tool in SQL for data analysis and reporting purposes, providing a flexible way to combine and correlate data from different tables.