Joins II: OUTER (LEFT/RIGHT/FULL), SELF JOIN & CROSS JOIN

Inner joins hide the no-match rows

An inner join only returns rows that match on both sides. That is usually what you want — but sometimes the *absence* of a match is the answer. "Which work centers produce no products?" or "which customers placed no orders?" An inner join silently drops exactly the rows you are looking for.

An outer join keeps the unmatched rows and fills the missing side with NULL:

- LEFT OUTER JOIN — all rows from the left (first) table, matched rows from the right; right columns are NULL where there is no match. - RIGHT OUTER JOIN — the mirror image (all rows from the right table). - FULL OUTER JOIN — all rows from both tables, matched where possible, NULL-filled where not.

LEFT vs. RIGHT is often just which table you wrote first: A LEFT JOIN B equals B RIGHT JOIN A. The real decision is whether you need an outer join *at all* — only when the no-match rows carry business meaning.

Worked example: products per work center, including the zeros

*Question (exam 6-64):* list every work center and how many products it makes; show centers with zero products as 0. Work center 4 ('Stationery') deliberately makes nothing.

SELECT w.wc_name, COUNT(p.product_id) AS total_products
FROM   work_centers w
LEFT JOIN products p ON p.wc_id = w.wc_id
GROUP BY w.wc_name;

Two things make this work together:

1. LEFT JOIN keeps 'Stationery' even though no product matches — its products columns come back NULL.
2. COUNT(p.product_id), not COUNT(*). COUNT(*) counts the row itself, so 'Stationery' would wrongly report 1. COUNT(p.product_id) counts non-null product ids, so the all-NULL row counts as 0. This pairing — *outer join + COUNT of a column from the outer side* — is the canonical "include the zeros" idiom.

Self-join: a table joined to itself

A self-join handles a unary (recursive) relationship — a table whose foreign key points back into itself. In employees, manager_id references emp_id in the *same* table. To show an employee next to their manager's name you must treat the one physical table as two logical roles, using two aliases:

SELECT e.name AS employee, m.name AS manager
FROM   employees e          -- the 'employee' role
JOIN   employees m          -- the 'manager' role
       ON m.emp_id = e.manager_id;

Every column must be alias-qualified because both roles have an identical name column. e supplies the subordinate, m supplies the boss.

Worked example: managers of skilled employees (exam 6-62)

*Question:* list, alphabetically and without duplicates, the managers of employees who hold skill BS12.

SELECT DISTINCT m.name AS manager
FROM   employees e
JOIN   employees m ON m.emp_id = e.manager_id          -- self-join to the boss
JOIN   employee_skills es ON es.emp_id = e.emp_id      -- the subordinate's skills
WHERE  es.skill_code = 'BS12'
ORDER BY m.name;

The self-join gets the manager; the join to employee_skills filters subordinates to those with BS12; DISTINCT collapses a manager who supervises several BS12 holders into one row; ORDER BY alphabetizes. Result: Maya Stone and Noah Park.