-- Forgetting to use different aliases

SELECT employee_name, manager_name

FROM employees

JOIN employees ON manager_id = employee_id;

-- Use different aliases to distinguish roles

SELECT

e1.employee_name AS employee,

e2.employee_name AS manager

FROM employees e1

JOIN employees e2 ON e1.manager_id = e2.employee_id;

Self joins require different aliases (e1, e2) to reference the same table in different roles.

Without aliases, database cannot distinguish which table instance to use

-- Using INNER JOIN excludes top-level records

SELECT e1.name, e2.name AS manager

FROM employees e1

INNER JOIN employees e2 ON e1.manager_id = e2.id;

-- CEO with NULL manager_id is excluded

-- Use LEFT JOIN to include all employees

SELECT e1.name, e2.name AS manager

FROM employees e1

LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- CEO shows with NULL manager

Use LEFT JOIN for hierarchical data to include top-level records with NULL foreign keys.

INNER JOIN excludes employees without managers (CEO, top-level)

-- Confusing which alias represents which role

SELECT

e2.name AS employee, -- Wrong alias

e1.name AS manager -- Wrong alias

FROM employees e1

JOIN employees e2 ON e1.manager_id = e2.id;

-- Clear alias usage: e1 = employee, e2 = manager

SELECT

e1.name AS employee, -- e1 is the employee

e2.name AS manager -- e2 is the manager

FROM employees e1

JOIN employees e2 ON e1.manager_id = e2.id;

Be consistent: e1 for main entity (employee), e2 for related entity (manager).

Mixing up aliases leads to incorrect results

-- Not handling NULL foreign keys properly

SELECT COUNT(*) FROM employees e1