How to Use LEFT JOIN in SQL
The LEFT JOIN clause in SQL, also known as LEFT OUTER JOIN, is used to combine rows from two or more tables based on a related column between them. Unlike the INNER JOIN, a LEFT JOIN returns all the rows from the left table (the first table mentioned in the query) and the matching rows from the right table (the second table). If no match is found, NULL values are returned for columns from the right table.
Syntax of LEFT JOIN
SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
- table1: The first table in the join operation (the left table).
- table2: The second table in the join operation (the right table).
- common_column: The column that exists in both tables and is used to match the rows.
Example of LEFT JOIN
Consider two tables: employees and departments
Table 1: employees
employee_id | first_name | last_name | department_id |
---|---|---|---|
1 | John | Doe | 101 |
2 | Jane | Smith | 102 |
3 | Jim | Brown | NULL |
4 | Jill | White | 103 |
Table 2: departments
department_id | department_name |
---|---|
101 | HR |
102 | IT |
SQL Query
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
Result:
first_name | last_name | department_name |
---|---|---|
John | Doe | HR |
Jane | Smith | IT |
Jim | Brown | NULL |
Jill | White | NULL |
Explanation of the Query
The query selects the first_name, last_name and departments_name columns.
It performs a LEFT JOIN between the employees table and the departments table.
The ON keyword specifies the condition for the join, which is that the departments_id column in employees must match the departments_id in departments.
Since Jim Brown and Jill White do not have a department_id that matches any departments_id in the departments table, their rows will still be included in the result set, but with NULL values for the departments_name
Key Points:
- A LEFT JOIN includes all rows from the left table and matching rows from the right table.
- If no match is found in the right table, NULL values are returned for the columns from the right table.
- The LEFT JOIN is useful when you want to include all records from one table and retrieve matching records from another table, even when no matches exist.
Use Cases for LEFT JOIN:
- Incorporating Missing Data: Use LEFT JOIN when you want to include all records from the left table, even if there is no corresponding match in the right table.
- Combining Data with Optional Relationships: Use LEFT JOIN when one table has optional relationships with another (e.g., employees may or may not belong to a department).
Conclusion
The LEFT JOIN is a powerful SQL operation for combining data from two tables, ensuring that all records from the left table are returned, regardless of whether matching rows exist in the right table. It is particularly useful for including data with missing or optional relationships.