Mini Challenge 6 — LEFT JOIN, RIGHT JOIN, and DISTINCT
title: “Mini Challenge 6 — Topics 16-18” part: 1 topic_number: 0 slug: “mini-challenge-06” difficulty: “Beginner” prerequisites: “left-join, right-join, distinct” —
Mini Challenge 6 — LEFT JOIN, RIGHT JOIN, and DISTINCT
Overview
Practice outer joins to include non-matching rows and eliminate duplicates.
Challenge 1: All Departments Report
Show all departments with their employee count, including departments with zero employees. Show department_name and employee_count. Sort by employee_count (lowest first).
Hint
LEFT JOIN from departments to employees, use COUNT(e.employee_id) not COUNT(*).Challenge 2: Employees Without Projects
Find all employees who are NOT assigned to any project. Show employee_id, first_name, and last_name. Sort by last_name.
Hint
LEFT JOIN from employees to employee_projects, WHERE employee_projects.employee_id IS NULL.Challenge 3: All Projects with Employee Count
Show all projects with the number of employees assigned, including projects with zero employees. Show project_name and employee_count. Sort by project_name.
Hint
LEFT JOIN from projects to employee_projects, COUNT(ep.employee_id).Challenge 4: Unique Departments with Projects
Find all unique departments that have at least one employee working on a project. Show only department_name, no duplicates. Sort alphabetically.
Hint
Join employees and employee_projects to link departments to projects, use DISTINCT on department_name.Challenge 5: Complete Hiring Timeline
Show all possible combinations of departments and hire years (from employees table), even if no one was hired in that department during that year. Show department_name, hire_year, and employee_count.
This is advanced! You’ll need to:
- Get distinct hire years from employees
- Cross join with departments
- Left join back to employees to count matches
Hint
Create a subquery of distinct YEAR(hire_date), CROSS JOIN with departments, LEFT JOIN to employees with matching conditions.Answer Key
Challenge 1 Answer
```sql SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name ORDER BY employee_count, d.department_name; ``` **Expected output:** | department_name | employee_count | |-----------------|----------------| | Business Development | 0 | | Customer Support | 1 | | Data Science | 1 | | Legal | 1 | | Finance | 2 | | Human Resources | 2 | | Engineering | 3 | | ... | ... | All departments, including those with zero employees. **Key:** Use LEFT JOIN to keep all departments, COUNT(e.employee_id) to count only matched employees (not NULLs).Challenge 2 Answer
```sql SELECT e.employee_id, e.first_name, e.last_name FROM employees e LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id WHERE ep.employee_id IS NULL ORDER BY e.last_name; ``` **Expected output:** | employee_id | first_name | last_name | |-------------|------------|-----------| | 15 | Leo | Jackson | | 17 | Quinn | Martinez | | 18 | Mia | White | Employees not assigned to any project. **Key:** LEFT JOIN keeps all employees, WHERE IS NULL finds those without matches.Challenge 3 Answer
```sql SELECT p.project_name, COUNT(ep.employee_id) AS employee_count FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_id, p.project_name ORDER BY p.project_name; ``` **Expected output:** | project_name | employee_count | |--------------|----------------| | API Development | 2 | | CRM System | 2 | | Data Pipeline | 3 | | Marketing Campaign | 1 | | Mobile App | 3 | | Security Audit | 0 | | Website Redesign | 4 | | ... | ... | All projects with employee counts, including unassigned projects.Challenge 4 Answer
```sql SELECT DISTINCT d.department_name FROM departments d INNER JOIN employees e ON d.department_id = e.department_id INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id ORDER BY d.department_name; ``` **Expected output:** | department_name | |-----------------| | Engineering | | Finance | | Human Resources | | Marketing | | Sales | Only departments with employees on projects, no duplicates. **Key:** DISTINCT removes duplicate department names (multiple employees from same department on projects).Challenge 5 Answer
```sql -- Step 1: Get all distinct hire years WITH hire_years AS ( SELECT DISTINCT YEAR(hire_date) AS hire_year FROM employees ) -- Step 2: Cross join with all departments SELECT d.department_name, hy.hire_year, COUNT(e.employee_id) AS employee_count FROM departments d CROSS JOIN hire_years hy LEFT JOIN employees e ON d.department_id = e.department_id AND YEAR(e.hire_date) = hy.hire_year GROUP BY d.department_id, d.department_name, hy.hire_year ORDER BY d.department_name, hy.hire_year; ``` **Alternative without CTE (if you haven't learned CTEs yet):** ```sql SELECT d.department_name, hire_years.hire_year, COUNT(e.employee_id) AS employee_count FROM departments d CROSS JOIN ( SELECT DISTINCT YEAR(hire_date) AS hire_year FROM employees ) AS hire_years LEFT JOIN employees e ON d.department_id = e.department_id AND YEAR(e.hire_date) = hire_years.hire_year GROUP BY d.department_id, d.department_name, hire_years.hire_year ORDER BY d.department_name, hire_years.hire_year; ``` **Expected output (partial):** | department_name | hire_year | employee_count | |-----------------|-----------|----------------| | Business Development | 2017 | 0 | | Business Development | 2018 | 0 | | Business Development | 2019 | 0 | | Engineering | 2017 | 1 | | Engineering | 2019 | 1 | | Engineering | 2020 | 0 | | Engineering | 2021 | 1 | | ... | ... | ... | Every department-year combination, showing hiring activity (or lack thereof).Key Takeaways
• LEFT JOIN keeps all rows from the left table, even without matches • RIGHT JOIN keeps all rows from the right table • Use IS NULL to find non-matching rows in outer joins • COUNT(column) counts non-NULL values, COUNT(*) counts all rows • DISTINCT eliminates duplicate rows • CROSS JOIN creates all possible combinations (Cartesian product)
Up Next
Next topic: NULL Handling (IS NULL, IS NOT NULL, IFNULL) → part1_19_null_handling.md
Learn to work with NULL values properly!