About Lesson
SQL LEFT JOIN Keyword
The LEFT JOIN
keyword 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.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example: LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
This query selects employee names and their corresponding department names. It returns all employees, and the department name will be NULL if there is no matching department.
Sample Tables
Consider the following two tables:
Employees Table
| id | name | department_id |
|----|-------------|---------------|
| 1 | John Doe | 1 |
| 2 | Jane Smith | 2 |
| 3 | Mike Johnson| 1 |
| 4 | Emily Davis | 3 |
| 5 | Sarah Connor| 4 |
Departments Table
| id | department_name |
|----|-----------------|
| 1 | HR |
| 2 | Sales |
| 3 | IT |
Result of LEFT JOIN
| name | department_name |
|-------------|-----------------|
| John Doe | HR |
| Jane Smith | Sales |
| Mike Johnson| HR |
| Emily Davis | IT |
| Sarah Connor| NULL |
All employees are returned. Sarah Connor has no matching department, so department_name
is NULL.
Join the conversation