Left Join in SQL

SQL

A LEFT JOIN statement in SQL is used to retrieve records from two or more tables based on a related column, and it includes all records from the left table along with the matched records from the right table.

If there are no matching records in the right table, NULL values are included in the result set for columns from the right table.

Syntax:

SELECT *
FROM left_table
LEFT JOIN right_table ON left_table.column_name = right_table.column_name;

Explanation:
- left_table: The table from which you want to retrieve all records.
- right_table: The table you want to join with the left table.
- column_name: The column used to establish the relationship between the two tables.

Example:

Consider two tables, employees and departments:

Table: employees

emp_id	emp_name	emp_dept_id	emp_salary
1	John	101	50000
2	Mary	102	60000
3	Bob	101	55000

Table: departments

dept_id	dept_name
101	IT
102	Finance
103	Marketing

Table: projects

project_id	project_name	emp_id
1	Project A	1
2	Project B	2
3	Project C	3

Simple LEFT JOIN

Let’s aim to retrieve all employees and their corresponding departments, even if some employees don’t have assigned departments.

SELECT *
FROM employees
LEFT JOIN departments ON employees.emp_dept_id = departments.dept_id;

Output:

emp_id	emp_name	emp_dept_id	emp_salary	dept_id	dept_name
1	John	101	50000	101	IT
2	Mary	102	60000	102	Finance
3	Bob	101	55000	101	IT
4	Alice	103	52000	NULL	NULL

LEFT JOIN with WHERE Clause

SELECT *
FROM employees
LEFT JOIN departments ON employees.emp_dept_id = departments.dept_id
WHERE employees.emp_salary > 55000;

Output:

emp_id	emp_name	emp_dept_id	emp_salary	dept_id	dept_name
2	Mary	102	60000	102	Finance

Handling NULL Values

Understanding how NULL values are handled in a LEFT JOIN scenario.

SELECT emp_name, dept_name
FROM employees
LEFT JOIN departments ON employees.emp_dept_id = departments.dept_id;

Output:

emp_name	dept_name
John	IT
Mary	Finance
Bob	IT
Alice	NULL

LEFT JOIN with Multiple Tables:

Retrieve information about employees, their departments, and the projects they are working on.

SELECT *
FROM employees
LEFT JOIN departments ON employees.emp_dept_id = departments.dept_id
LEFT JOIN projects ON employees.emp_id = projects.emp_id;

Output:

emp_id	emp_name	emp_dept_id	emp_salary	dept_id	dept_name	project_id	project_name
1	John	101	50000	101	IT	1	Project A
2	Mary	102	60000	102	Finance	2	Project B
3	Bob	101	55000	101	IT	3	Project C

LEFT JOIN with Aggregation:

Calculate the average salary of employees in each department, including those without projects.

SELECT departments.dept_id, departments.dept_name, AVG(employees.emp_salary) as avg_salary
FROM departments
LEFT JOIN employees ON departments.dept_id = employees.emp_dept_id
GROUP BY departments.dept_id, departments.dept_name;

Output:

dept_id	dept_name	avg_salary
101	IT	52500
102	Finance	60000
103	Marketing	NULL