Course contentsShow
← Data Science
Lesson 945 of 2,14524. SQL JoinsFree lesson

Introduction to Self-Joins

Understand what a self-join is, why you'd join a table to itself, and how aliasing makes this possible.

Introduction to Self-Joins

You'll learn what a self-join is and why you'd ever join a table to itself.

What Is a Self-Join?

A self-join is when you join a table to itself—treating the same table as if it were two separate tables in your query. This might sound strange at first: why would you need to compare a table with itself? The answer lies in finding relationships between different rows in the same table.

Why Self-Join?

Self-joins are powerful when your data contains hierarchical or relational patterns within a single table. Common scenarios include:

  • Employee-manager relationships: An employees table where each employee has a manager_id pointing to another employee in the same table
  • Product recommendations: Finding pairs of products from the same products table
  • Sequential events: Comparing different timestamps or events within a single events table

Think of it like looking in a mirror: you're still you, but now you can observe yourself from a different angle. Similarly, a self-join lets you examine one set of rows against another set from the same table.

The Key: Table Aliases

Self-joins are only possible because of table aliases. You give the same table two different temporary names (aliases), allowing SQL to treat them as distinct entities during the join:

FROM employees AS e1
INNER JOIN employees AS e2
  ON e1.manager_id = e2.employee_id

Here, e1 and e2 both refer to employees, but SQL treats them as separate "copies" for joining purposes.

Key Takeaway: A self-join joins a table to itself using aliases, enabling you to find relationships between different rows within the same table—essential for hierarchical data and row-to-row comparisons.