A self-join joins a table to itself under two aliases. categories.parent_id points at another categories row, so a product's department takes two joins, one hop per foreign key; the second is a LEFT JOIN because top-level categories have no parent:
SELECT p.sku, cat.name AS category, top.name AS department
FROM products AS p
JOIN categories AS cat ON cat.id = p.category_id
LEFT JOIN categories AS top ON top.id = cat.parent_id
WHERE p.id IN (2, 8);
SELECT name FROM categories WHERE id NOT IN (SELECT parent_id FROM categories);+-----------+-------------+------------+ | sku | category | department | +-----------+-------------+------------+ | BK-SQL-01 | Databases | Books | | AC-STK-01 | Accessories | NULL | +-----------+-------------+------------+ 2 rows in set (0.000 sec) Empty set (0.001 sec)
Make the last join inner and the sticker pack disappears. The second query wants the leaf categories and finds none, although four exist. The subquery returns NULL, 1, 1, 1, NULL, and x NOT IN (..., NULL) is never true. Never use NOT IN on a nullable column; NOT EXISTS (SELECT 1 FROM categories AS k WHERE k.parent_id = c.id) returns all four.
Beware fan-out when joining two one-to-many children of one parent: product 1's two order lines and two reviews join into four rows, and SUM(quantity) says 6 instead of 3. Aggregate each child in a derived table first (Derived Tables and LATERAL). For trees of any depth, use a recursive CTE (Recursive CTEs).