AI Reading
Quick summary of this article
Chapter 8: Mastering SQL Joins (INNER, LEFT, RIGHT, SELF)
AI Reading
Quick summary of Chapter 8
Relational databases derive their power from connecting related entities. In this chapter, you will master all major join types, understand Venn-diagram join mechanics, and query organizational hierarchies using recursive self-joins.
- INNER JOIN: Extract records that have corresponding matches in both participating tables.
- LEFT JOIN: Preserve all parent rows from the left table while extracting matching child rows or NULLs.
- RIGHT JOIN: Preserve child rows from the right table while fetching matching parent data.
- CROSS JOIN: Calculate Cartesian products of all row combinations.
- SELF JOIN: Join a table to itself to model organizational reporting lines between employees and supervisors.
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
| Join Type | Venn Diagram Concept | Output Behavior |
|---|---|---|
INNER JOIN |
Intersection of A and B | Returns rows only when there is a match in both tables. |
LEFT JOIN |
All of Table A + Overlap with B | Returns all rows from the Left table; unmatched Right columns return NULL. |
RIGHT JOIN |
All of Table B + Overlap with A | Returns all rows from the Right table; unmatched Left columns return NULL. |
CROSS JOIN |
Cartesian Product (A x B) | Combines every row from Table A with every row from Table B. |
SELF JOIN |
Table A joined with Table A | Joins a table to itself using table aliases to model hierarchies. |
👥 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 preferUNION ALLwhen 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 ....