A self join is when a table is joined with itself. To join the same table multiple times, you must use table aliases to differentiate each instance in the query.

1. Why Use Aliases in Self Joins

1
• MySQL requires each table in a JOIN to have a unique reference.
2
• Aliases allow the same table to appear multiple times in the query.
3
• Aliases clarify which instance of the table each column belongs to.

2. Common Use Cases

1
• Representing hierarchical data (e.g., employees reporting to managers).
2
• Comparing rows within the same table (e.g., finding overlapping dates or relationships).
3
• Performing multiple joins to the same lookup table for different roles or attributes.

Example: Employee → Manager → Mentor

In this example, the employees table is joined twice using aliases m for manager and n for mentor. Each alias allows you to reference different relationships within the same table.

3. Key Points

1
• Always assign a unique alias for each instance of the table.
2
• Use meaningful alias names to make the query readable.
3
• Self joins can be combined with INNER, LEFT, or RIGHT JOIN depending on which rows should be preserved.

In summary: Joining a table with itself multiple times requires aliases to differentiate each instance. This allows you to query hierarchical or relational data within a single table effectively.

Difficulty: 5/10

Topics: self-join, aliases, query performance

Scenario Questions

0-2 years experience

1
We have an employee table (id, name, manager_id). How would you write a query to list each employee together with their manager's name using aliases?
2
Given a products table with product_id and category_id, write a query that joins the products table to itself to find pairs of products in the same category, using aliases.
3
If you need to retrieve each order along with the previous order for the same customer from an orders table, how would you self‑join with aliases?

2-5 years experience

1
Our reporting feature needs to show a three‑level category hierarchy stored in a single categories table (id, name, parent_id). Explain how you'd join the table to itself multiple times with aliases and any pitfalls you might encounter.
2
We added a 'duplicate_of' column to the users table that points to another user record. A bug now returns duplicate rows. Walk me through how you'd debug the self‑join query using aliases.
3
When we added a self‑join to compute cumulative sales per region, query performance dropped. What trade‑offs would you consider when rewriting that query?

5-8 years experience

1
Our analytics pipeline processes millions of rows daily and uses a self‑join on a transactions table to find linked transactions. How would you design the query and indexes to keep it performant at scale?
2
We need a materialized view that flattens a multi‑level org chart stored in an employees table using self‑joins. Discuss design choices, recursion limits, and maintenance strategy.
3
During a migration to a sharded architecture, self‑joins across shards become problematic. How would you refactor the logic that currently uses multiple self‑joins with aliases?

8+ years experience

1
Our company is consolidating several legacy services that each use complex self‑joins for permission inheritance. As a staff engineer, how would you create a unified data model that reduces the need for multiple self‑joins while preserving query flexibility?
2
We plan to move from MySQL to a distributed SQL engine. What architectural considerations would you raise regarding existing queries that rely heavily on self‑joins with aliases, especially concerning data locality and consistency?
3
How would you establish a cross‑team guideline for writing self‑joins to ensure maintainability and avoid alias collisions in a large codebase?

Follow-up Questions

❓What would happen if you omitted the ON condition for one of the aliases?
❓How does adding an index on the join columns affect the execution plan?
❓Can you think of a scenario where a self‑join could be replaced by a window function?