Previous

SQL LEFT JOIN

The LEFT JOIN (or LEFT OUTER JOIN) in SQL is used to combine rows from two or more tables. It returns all the rows from the left table (the first table) and the matching rows from the right table (the second table). If there is no match, the result will contain NULL values for columns from the right table.

 

Syntax of LEFT JOIN

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
  • table1: Left table in the join.
  • table2: Right table in the join.
  • column: The column that links the two tables.

 

Example

Consider the following two tables:

Employees Table

employee_id first_name last_name
1 John Doe
2 Jane Smith
3 Mike Johnson

Departments Table

department_id employee_id department_name
101 1 HR
102 2 IT

In this case, the employee with employee_id = 3 does not have a department assigned.

To get a list of employees and their respective departments, using LEFT JOIN:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.employee_id = departments.employee_id;

Output:

first_name last_name department_name
John Doe HR
Jane Smith IT
Mike Johnson NULL

In this result:

  • The LEFT JOIN includes all employees from the employees table.
  • It returns matching department names where the employee_id matches in the departments table.
  • Since Mike Johnson does not have a department, the value for department_name is NULL.

 

LEFT JOIN with WHERE Clause

You can also filter the results using a WHERE clause after performing a LEFT JOIN. For example:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.employee_id = departments.employee_id
WHERE departments.department_name IS NULL;

This query will return all employees who are not assigned to any department:

Output:

first_name last_name department_name
Mike Johnson NULL

 

LEFT JOIN with Multiple Tables

You can join more than two tables using multiple LEFT JOINs. For example, adding a projects table:

SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
LEFT JOIN departments ON employees.employee_id = departments.employee_id
LEFT JOIN projects ON employees.employee_id = projects.employee_id;

This query would return a list of employees with their departments and projects (if any).

 

Key Points

  • LEFT JOIN returns all rows from the left table and the matching rows from the right table.
  • If no match is found, the right table's columns will contain NULL.
  • It is useful when you want to include all records from the left table, even when there is no match in the right table.
  • You can combine LEFT JOIN with WHERE or AND clauses to filter the result.

 

Summary

The LEFT JOIN ensures that all rows from the left table are returned, even if there is no match in the right table. It is helpful when you want to retrieve data from one table while including rows from another table, even when no corresponding data exists in the second table.

Previous