Chapter 8: Mastering SQL Joins (INNER, LEFT, RIGHT, SELF)

Welcome to Chapter 8. Relational databases normalize data across separate tables to eliminate redundancy. To answer business questions like “Which department does Steven King belong to?” or “Who is Nancy Greenberg’s manager?”, you must combine related tables using SQL Joins.

In this chapter, we will master all major join types, examine hierarchical self-joins, and explore set combination operations using UNION.

🔗 8.1 Visualizing Join Types with Venn Diagrams

👥 8.2 INNER JOIN Example (Departments & Employees)

An INNER JOIN returns rows where the foreign key in employees matches the primary key in departments:

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    emp.salary,
    dept.department_name,
    dept.location_id
FROM employees emp
INNER JOIN departments dept ON emp.department_id = dept.department_id
ORDER BY dept.department_name ASC;

💬 8.3 LEFT JOIN Example (Finding Departments with Zero Employees)

If you want to view every department, including departments that currently have no employees assigned, an INNER JOIN will exclude them. You must use a LEFT JOIN starting from departments:

SELECT 
    dept.department_id,
    dept.department_name,
    COUNT(emp.employee_id) AS total_employees
FROM departments dept
LEFT JOIN employees emp ON dept.department_id = emp.department_id
GROUP BY dept.department_id, dept.department_name
ORDER BY total_employees ASC;

🧳 8.4 SELF JOIN Example (Reporting Hierarchy)

In our corporate schema, managers are also employees stored in the same employees table. By aliasing the table twice (emp for the employee, mgr for the manager), we can query the reporting tree:

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    emp.salary AS emp_sal,
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    mgr.salary AS mgr_sal
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id
ORDER BY mgr.first_name ASC;

📜 8.5 Set Operations: UNION vs UNION ALL

While JOINs combine columns horizontally, set operations combine result rows vertically:

• UNION: Appends two queries and executes an internal deduplication pass to eliminate duplicate rows.
• UNION ALL: Appends two queries without deduplication. Always prefer UNION ALL when results are already known to be unique for maximum query speed.

❓ Frequently Asked Questions (FAQ)

1. Why is Steven King missing from the SELF JOIN result?

Steven King is the company President; his manager_id is NULL. Because an INNER JOIN requires a match in both tables, NULL cannot match any employee_id. To include Steven King with a NULL manager, use a LEFT JOIN: FROM employees emp LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id.

2. Can you join more than two tables in MySQL?

Yes! You can chain multiple joins together sequentially: SELECT ... FROM table1 JOIN table2 ON ... JOIN table3 ON ....