Right Join - tricky interview questions

 Here are some tricky interview questions involving RIGHT JOINs, which will test your understanding of the concept and its nuances:

Question 1: Basic Understanding

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

A: A LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no match, the result is NULL on the right side. A RIGHT JOIN returns all records from the right table and the matching records from the left table. If there is no match, the result is NULL on the left side.

Question 2: Handling NULLs

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

• 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
RIGHT JOIN departments d ON e.department_id = d.id;

Question 3: Filtering with RIGHT JOIN

Q: Write a query to find departments that do not have any employees assigned.

A:

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

Question 4: Multiple RIGHT JOINs

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

• projects table:

id	employee_id	project_name
1	1	Project A
2	2	Project B

A:

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

Question 5: Finding Unmatched Records

Q: Write a query to find projects that do not have any employees assigned.

A:

SELECT p.project_name
FROM projects p
LEFT JOIN employees e ON p.employee_id = e.id
WHERE e.id IS NULL;

Question 6: RIGHT JOIN with Aggregation

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
RIGHT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;

Question 7: Complex Filtering

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

A:

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

Question 8: RIGHT JOIN with Self Join

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

A:

SELECT d.department_name, m.name AS manager
FROM departments d
RIGHT JOIN employees e ON d.id = e.department_id
LEFT JOIN employees m ON e.manager_id = m.id;

Question 9: RIGHT JOIN with Multiple Tables

Q: Write a query to list all departments and the names of employees who are working on projects in those departments. Include departments with no employees working on any projects.

A:

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

Question 10: RIGHT JOIN with Subqueries

Q: Write a query to find departments with more than 2 employees, including departments with no employees, using RIGHT JOIN.

A:

SELECT d.department_name
FROM departments d
RIGHT JOIN (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 2
) e_counts ON d.id = e_counts.department_id
OR e_counts.department_id IS NULL;

These questions cover a variety of scenarios and edge cases involving RIGHT JOINs, ensuring a deep understanding of the concept.