Self-Joins & Multiple-Table Joins

📌 Definition:

📖 Detailed Explanation:

Some relationships are hierarchical or peer-to-peer within a single table: an employees table where manager_id points to another row in employees, or a categories table with a parent_id. A self-join treats the one table as two logical copies with different aliases (e, m) and joins them on the relationship (e.manager_id = m.id). Multiple-table joins are the everyday reality of validation: an end-to-end order record spans users, orders, order_items and products, so you chain joins to reconstruct it. The discipline with many joins is to ensure each join's ON condition is correct and sufficiently constraining, and to choose INNER vs LEFT deliberately per join based on whether that relationship is optional. Order of joins doesn't change INNER JOIN results (the optimizer decides execution), but mixing INNER and LEFT joins requires care because a later INNER JOIN can eliminate the NULL-extended rows a LEFT JOIN preserved.

🔑 Key Points:

• Self-join = one table joined to itself via two aliases (e.g. employee ↔ manager)
• Used for hierarchies and intra-table relationships (manager_id, parent_id)
• Multi-table joins chain relationships to rebuild an end-to-end record
• Choose INNER vs LEFT per join based on whether that relationship is optional
• A later INNER JOIN can cancel out rows a preceding LEFT JOIN preserved

🌍 Real-World Example:

📎 Code Example:

-- Self-join: each employee with their manager's name
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;   -- LEFT keeps the CEO (no manager)

-- Find employees whose manager is in a different department (rule violation)
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.department_id <> m.department_id;

-- Multi-table: full order picture across four tables
SELECT u.email, o.id AS order_id, p.name AS product, oi.quantity
FROM users u
JOIN orders o       ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id = 5001;