Left Join - Tricky interview questions

 Here are some tricky interview questions involving LEFT JOINs, designed to test your understanding of the concept and its nuances:

Question 1: Basic Understanding

Q: What is the difference between LEFT JOIN and INNER JOIN?

A: A LEFT JOIN returns all records from the left table, along with the matching records from the right table. If there is no match, the result is NULL from the right table. An INNER JOIN, on the other hand, returns only the records that have matching values in both tables.

Question 2: Handling NULLs

Q: Given the following tables, write a query to return all employees and their department names, including employees without a department.

• 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

A:

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

Question 3: Filtering with LEFT JOIN

Q: Write a query to find employees who are not assigned to any department.

A:

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

Question 4: Multiple LEFT JOINs

Q: Given an additional projects table, write a query to return all employees with their department names and project names, including employees without a department or project.

• projects table:

id	employee_id	project_name
1	1	Project A
2	2	Project B

A:

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;

Question 5: Finding Unmatched Records

Q: Write a query to find employees who are either not assigned to any department or not assigned to any project.

A:

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;

Question 6: Self Join

Q: Using the same employees table, write a query to return each employee and their manager's name. Assume manager_id refers to the id of the employee who is their manager.

A:

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

Question 7: Aggregation with LEFT JOIN

Q: Write a query to find the total number of employees in each department, including departments with no employees.

A:

SELECT d.department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;

Question 8: Complex Filtering

Q: Write a query to find all departments that have at least one employee and no employee is assigned to a project.

A:

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

These questions cover a range of complexities and edge cases that can arise with LEFT JOINs, ensuring a comprehensive understanding of the topic.