SQL LEFT Join is a type of join in SQL (Structured Query Language) that combines rows from two or more tables based on a related column between them. The LEFT Join returns all the rows from the left table (the first table in the query), and matching rows from the right table (the second table in the query). If there is no match in the right table, the result will contain NULL values for the columns from the right table.
A LEFT Join is useful when you want to return all the rows from one table, and the matching rows from another table. It is also used to return all the rows from one table, even if there is no matching record in the second table.
SQL LEFT Join Syntax
The basic syntax of the SQL LEFT Join is as follows:
SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
In this syntax:
- SELECT clause specifies the columns to be returned in the result set.
- FROM clause specifies the left table (table1) in the join.
- LEFT JOIN clause specifies the right table (table2) in the join and the join condition using the ON clause.
- ON clause specifies the join condition that relates the columns from both tables.
SQL LEFT Join Example
Consider two tables: “employees” and “departments”. The “employees” table has the following data:
EmployeeID | EmployeeName | DepartmentID |
---|---|---|
E001 | John | D001 |
E002 | Jane | D002 |
E003 | Bob | D003 |
E004 | Alice | D004 |
E005 | Eve | D005 |
And the “departments” table has the following data:
DepartmentID | DepartmentName |
---|---|
D001 | IT |
D002 | Marketing |
D003 | Sales |
If we want to get the names of all employees and their respective departments, we can use the following SQL LEFT Join statement:
SELECT employees.EmployeeName, departments.DepartmentName FROM employees LEFT JOIN departments ON employees.DepartmentID = departments.DepartmentID;
The result of the above query will be:
EmployeeName | DepartmentName |
---|---|
John | IT |
Jane | Marketing |
Bob | Sales |
Alice | NULL |
Eve | NULL |
In the result, all employees are displayed, and the departments that don’t match with any employee are displayed as NULL values in the “DepartmentName” column.
Conclusion
SQL LEFT Join is a type of join in SQL that returns all the rows from the left table and matching rows from the right table. If there is no match in the right table, the result will contain NULL values for the columns from the right table. This type of join is useful when you want to return all the rows from one table, and the matching rows from another table, or when you want to return all the rows from one table, even if there is no matching record in the second table.