Q: How many pairs does id < id produce?

The query finds all pairs of employees in the same department. e1.id < e2.id prevents duplicates (Alice-Bob and Bob-Alice would otherwise both appear) and prevents self-pairs (Alice-Alice). In Eng there is one pair: (Alice, Bob). Carol is alone in HR and has no partner. Result: 1 row.

Q: Matching rows by price - how many pairs appear?

The self join finds pairs of distinct products with the same price. Alpha and Gamma both cost 10.00, so they form a pair. p1.id < p2.id ensures each pair appears exactly once (order: id 1 then id 3). Beta has a unique price and forms no pair.

Q: Chaining two self joins up the hierarchy

The query chains two self joins to walk two levels up the hierarchy. The first join finds each employee's direct manager. The second join finds that manager's manager. Carol's manager is Bob; Bob's manager is Alice. So Carol's grand-manager is Alice. Bob has a manager (Alice) but Alice has no manager, so Bob's row produces no match in the second join and is excluded.

Q: Self join without aliases - what breaks?

Without aliases, every column reference is ambiguous - the database cannot tell whether 'employees.id' refers to the left copy or the right copy of the table. The query will fail at parse time with an error like 'table name employees is specified more than once' or 'column reference is ambiguous'. Aliases (e.g. e1, e2 or emp, mgr) are not optional in a self join - they are syntactically required.

Q: Which levels survive two INNER JOINs?

Each INNER JOIN requires a matching row. The first join drops anyone with no direct manager (NULL manager_id). The second join drops anyone whose direct manager has no manager of their own - so employees just one level below the top are also excluded. Only employees who are at least two levels deep in the hierarchy survive both joins. Use LEFT JOINs if you want to preserve all employees.

Question 1

Who gets dropped when manager_id is NULL?

Accepted Answer

The table is joined to itself: 'e' represents the employee and 'm' represents the manager. For Bob (manager_id=1), the join finds id=1 which is Alice. Same for Carol. Alice has NULL manager_id, so her row finds no match in the INNER JOIN and is excluded. Use LEFT JOIN if you want Alice in the result with NULL for manager.

Question 2

Joining a table to itself

Accepted Answer

A self join is just a regular JOIN where both sides reference the same table. You must use aliases (e.g. e1, e2) so the database can distinguish which copy of the table each column comes from. Self joins are common for hierarchical data (employee-manager) and for finding pairs within the same table (e.g., employees in the same department).

Question 3

Does the top of the hierarchy show up?

Accepted Answer

LEFT JOIN preserves all rows in the left table even when no match exists in the right copy. Alice has no manager (NULL manager_id), so the join produces NULL for m.name - she still appears in the result. Bob and Carol both resolve to Alice as their manager.

Question 4

How many pairs does id < id produce?

Accepted Answer

The query finds all pairs of employees in the same department. e1.id < e2.id prevents duplicates (Alice-Bob and Bob-Alice would otherwise both appear) and prevents self-pairs (Alice-Alice). In Eng there is one pair: (Alice, Bob). Carol is alone in HR and has no partner. Result: 1 row.

Question 5

Matching rows by price - how many pairs appear?

Accepted Answer

The self join finds pairs of distinct products with the same price. Alpha and Gamma both cost 10.00, so they form a pair. p1.id < p2.id ensures each pair appears exactly once (order: id 1 then id 3). Beta has a unique price and forms no pair.

Question 6

Chaining two self joins up the hierarchy

Accepted Answer

The query chains two self joins to walk two levels up the hierarchy. The first join finds each employee's direct manager. The second join finds that manager's manager. Carol's manager is Bob; Bob's manager is Alice. So Carol's grand-manager is Alice. Bob has a manager (Alice) but Alice has no manager, so Bob's row produces no match in the second join and is excluded.

Question 7

Self join without aliases - what breaks?

Accepted Answer

Without aliases, every column reference is ambiguous - the database cannot tell whether 'employees.id' refers to the left copy or the right copy of the table. The query will fail at parse time with an error like 'table name employees is specified more than once' or 'column reference is ambiguous'. Aliases (e.g. e1, e2 or emp, mgr) are not optional in a self join - they are syntactically required.

Question 8

Which levels survive two INNER JOINs?

Accepted Answer

Each INNER JOIN requires a matching row. The first join drops anyone with no direct manager (NULL manager_id). The second join drops anyone whose direct manager has no manager of their own - so employees just one level below the top are also excluded. Only employees who are at least two levels deep in the hierarchy survive both joins. Use LEFT JOINs if you want to preserve all employees.

Self Joins