SQL JOIN LEFT JOIN
The SQL JOIN LEFT JOIN statement is used to join two or more tables based on a related column between them. In a LEFT JOIN, all the records from the left table are returned along with matching records from the right table. If there are no matches, the result will contain NULL values.
Syntax
The basic syntax of the SQL JOIN LEFT JOIN statement is as follows:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Example
Suppose we have two tables, employees and departments, with the following data:
employees table
| id | name | department_id |
|---|---|---|
| 1 | John | 2 |
| 2 | Jane | 1 |
| 3 | Mark | 3 |
| 4 | Emily | NULL |
departments table
| id | name |
|---|---|
| 1 | Sales |
| 2 | Marketing |
| 3 | IT |
We want to get a list of all employees and their department names:
SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
Output
The output of the above query will be:
| name | name |
|--------|-----------|
| John | Marketing |
| Jane | Sales |
| Mark | IT |
| Emily | NULL |
Explanation
In the above example, we used the LEFT JOIN statement to join the employees table with the departments table on the department_id column of the employees table and the id column of the departments table.
The result of the query includes all the records from the employees table along with matching records from the departments table, even if there are no matches (in the case of Emily).
Use
The LEFT JOIN statement is useful when you want to get all the records from one table along with matching records (if any) from another table. It is also useful in cases where you want to get a list of all records, even if there are no matches in a related table.
Important Points
- In a LEFT JOIN, all the records from the left table are returned along with matching records from the right table.
- If there are no matches, the result will contain NULL values.
- The JOIN condition is specified using the ON keyword and should be based on a related column between the two tables.
Summary
The SQL JOIN LEFT JOIN statement is used to join two or more tables based on a related column between them. It returns all the records from the left table along with matching records from the right table. If there are no matches, the result will contain NULL values. The JOIN condition is specified using the ON keyword and should be based on a related column between the two tables.