Left Join
In SQL, a Join operation combines rows from two or more tables based on a related column between them. There are several types of Join operations, such as Inner Join, Left Join, Right Join, and Full Join. In this article, we will be focusing on one of these operations: The Left Join.
What is Left Join in SQL?
The Left Join in SQL returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
Imagine you have two tables, one that lists all employees in a company (table1), and another one that lists all departments in the same company (table2). A Left Join operation would return all employees, and any matching department records. If an employee is not assigned to any department, the result is NULL on the department side.
Syntax
The basic syntax for Left Join in SQL is as follows:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example
Let's consider the following two tables: Orders and Customers.
Orders table:
| OrderID | CustomerID | OrderAmount |
|---|---|---|
| 1 | 3 | 30 |
| 2 | 1 | 20 |
| 3 | 2 | 10 |
| 4 | 5 | 50 |
Customers table:
| CustomerID | CustomerName | ContactNumber |
|---|---|---|
| 1 | John | 1234567890 |
| 2 | Jane | 2345678901 |
| 3 | Bob | 3456789012 |
If we want to match and combine the rows from Orders and Customers tables, we can use the following SQL statement:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderAmount
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
This will return the following data:
| OrderID | CustomerName | OrderAmount |
|---|---|---|
| 1 | Bob | 30 |
| 2 | John | 20 |
| 3 | Jane | 10 |
| 4 | NULL | 50 |
As you can see, the Left Join operation returns all records from the left table (Orders), and the matched records from the right table (Customers). If there is no match, the result is NULL on the right side.
Conclusion
In this article, we learned about the Left Join operation in SQL. We covered what it is, its syntax, and how to use it with an example.
The Left Join operation is a very useful tool that can help you to combine data from two or more tables based on a related column between them. Remember, it returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
Keep practicing with different datasets and scenarios to get a better understanding of how the Left Join operation works. Happy coding!