Self JOIN in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Self JOIN in SQL?

A Self JOIN is when a table is joined with itself. It helps compare rows within the same table by treating it as if there were two tables.

beginner

Why would you use a Self JOIN?

You use a Self JOIN to find relationships between rows in the same table, like finding an employee's manager in an employee table.

beginner

In a Self JOIN, why do we use table aliases?

Table aliases give the same table different names so SQL can tell which copy of the table you mean when comparing rows.

intermediate

Write a simple Self JOIN query to find employees and their managers from an employee table with columns (id, name, manager_id).

SELECT e.name AS Employee, m.name AS Manager FROM employee e LEFT JOIN employee m ON e.manager_id = m.id;

beginner

What happens if you forget to use aliases in a Self JOIN?

SQL will get confused because it can't tell which instance of the table you mean, causing errors or unexpected results.

What does a Self JOIN do?

A Joins two different tables

B Joins tables with no common columns

C Joins a table with itself

D Joins tables only on primary keys

A Self JOIN joins a table with itself to compare rows within the same table.

Why are aliases important in a Self JOIN?

A To give the same table different names for clarity

B To avoid using WHERE clause

C To speed up the query

D To rename columns

Aliases let you treat the same table as two different tables in the query.

In a Self JOIN, if you want to find an employee's manager, which column would you join on?

A employee.id = manager.id

B employee.manager_id = manager.id

C employee.name = manager.name

D employee.id = manager.manager_id

You join employee.manager_id to manager.id to link employees to their managers.

What type of JOIN is commonly used in Self JOIN to include employees without managers?

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D FULL JOIN

LEFT JOIN includes all employees even if they don't have a manager.

Which of these is a valid alias usage in a Self JOIN?

A FROM employee AS e JOIN employee AS e ON e.manager_id = e.id

B FROM employee JOIN employee ON employee.manager_id = employee.id

C FROM employee JOIN employee

D FROM employee e JOIN employee m ON e.manager_id = m.id

Using different aliases like e and m helps SQL distinguish the two instances of the same table.

Explain what a Self JOIN is and give a simple example scenario where it is useful.

Think about how one table can relate to itself.

Describe the role of table aliases in a Self JOIN and what problems they solve.

Why can't you just write the table name twice without aliases?