Left Join - tricky examples

 LEFT (OUTER) JOIN is a common SQL join operation used to combine rows from two or more tables based on a related column between them. A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

Here are some tricky examples of using LEFT JOIN:

Example 1: Handling NULLs in Conditions

Imagine you have two tables: employees and departments.

• employees table:

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	NULL
4	David	4

• departments table:

id	department_name
1	HR
2	Engineering
3	Sales

You want to get a list of all employees with their department names, but also include employees who are not assigned to any department.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

This will result in:

name	department_name
Alice	HR
Bob	Engineering
Charlie	NULL
David	NULL

Example 2: Filtering with LEFT JOIN

If you want to find all employees who are not assigned to any department, you need to use a WHERE clause carefully:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

This ensures you only get employees without a department:

name
Charlie
David

Example 3: LEFT JOIN with Multiple Conditions

Consider you have another table projects and you want to find employees who are either not assigned to any department or not assigned to any project.

• projects table:

id	employee_id	project_name
1	1	Project A
2	2	Project B

Here's how to approach this:

SELECT e.name, d.department_name, p.project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id;

If you specifically want employees without a department or project:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id
WHERE d.id IS NULL OR p.id IS NULL;

Example 4: Self Join with LEFT JOIN

Sometimes, you might need to join a table to itself. For instance, consider a scenario where you want to find employees and their managers from the same table.

• employees table:

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	2
4	David	2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

This results in:

employee	manager
Alice	NULL
Bob	Alice
Charlie	Bob
David	Bob

These examples demonstrate various tricky scenarios using LEFT JOIN, highlighting how to handle NULLs, filter results, use multiple conditions, and perform self joins.