Self joins can be expensive on large tables. Index foreign key columns. Consider recursive CTEs for deep hierarchies.

Index self-referencing columns

Use WHERE to limit rows before join

Consider materialized views for complex hierarchies

Use recursive CTEs for deep hierarchies

Forgetting to use different aliases

Using INNER JOIN when LEFT JOIN needed (excludes top level)

Not indexing self-referencing foreign keys

Confusing which alias represents which role