In SQL (Structured Query Language), a JOIN operation combines rows from two or more tables based on a related column between them. A self-JOIN is a type of JOIN where a table is joined with itself. Essentially, it’s when you treat the same table as two separate tables, each of which temporarily holds different subsets of the data. This is typically done when there are different pieces of information in the same table that need to be associated with each other.
Here’s a simple example to demonstrate how it works. Imagine you have a table of employees in a company, like this:
In this table, each employee has an ID and a manager, who is also an employee. The
ManagerID column refers to the
ID of the employee’s manager.
If you want to find out the names of all employees and the names of their respective managers, you’d have to use a self-JOIN because the information you need is all in the same table.
Here’s how you can do it:
In this query,
e2 are aliases for the
Employees table. The
INNER JOIN condition
e1.ManagerID = e2.ID specifies that we’re looking for rows where the manager’s ID in the first instance of the table (
e1) matches the employee’s ID in the second instance of the table (
e2). This allows us to associate each employee with their manager, even though they’re all in the same table.
The result would be something like this: