What is a self-join and when is it used?

A self-join joins a table to itself, treating the same table as two separate tables with different aliases. Used to query hierarchical or relational data within a single table. Classic example: an employees table with a manager_id FK referencing id in the same table. SELECT e.name, m.name as manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. Other use cases: finding pairs of rows with a relationship (employees in the same department: e1.dept_id = e2.dept_id AND e1.id < e2.id), or comparing consecutive rows (with ORDER BY and ROW_NUMBER). Recursive CTEs are often a cleaner alternative for deep hierarchies.