SQL self join

SELF JOIN is the type of join, where a table is joined with itself as if it were two separate tables. It is useful where a table contains hierarchical data or when comparing data within the same table.

The syntax of the SQL self join generally looks like this :

SELECT column_name(s)
FROM table1 t1
JOIN table1 t2 ON t1.column_name = t2.column_name;

OR

SELECT column_name(s)
FROM table1 t1, table1 t2
WHERE condition;
        

• 'table1' and 'table2' refer to the two tables being joined together.

• 'column_name(s)' refer to the selected columns from the tables being joined together.

• 'Aliases' names (t1, t2) is used to give different names to the same table.

• 'ON' clause is used to specify the conditions for joining two or more tables together.

• 'WHERE' clause is used to specify the conditions to filter the data.

As seen in above image, SQL self join produces a result set that includes each rows from 'Table-A' along with any matching rows from the same table ( 'Table-A' ).

Here's an example of how you might use the SQL self join :

Let's suppose we have a table :

[a] Employees Table :

→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Department_Id].

1. Let's use SQL self join to retrieve the department id's and their respective employee names by joining the [Employees] with itself based on matching values in the same table.

2. Please run the following SQL statement :

SELECT Emp1.Employee_Name, Emp1.Department_Id, Emp2.Employee_Name
FROM [Employees] Emp1
JOIN [Employees] Emp2
ON Emp1.Department_Id = Emp2.Department_Id
        

3. Self join creates two copies of the same table and treats it as two separate tables. it uses 'aliases' table name to differentiate between them that allows the columns from each copy of the same table to be referenced separately within the query.

4. Above statement selects all the columns from the same table based on matching values in the 'DepartmentId' column. It returns all the rows from a table and the matching rows from the same table as the output.

• As you have seen, Only specific selected columns ( [Employee_Name], [Department_Id] from "Employees" table and [Employee_Name] from another copy of the same table ) are returned.

• It returns all the rows from a table and all the matching rows from another copy of the same table based on the matching values in the 'DepartmentId' column.

• As marked it screenshot above, It returned 4 rows where the [Department_Id] column contained the values '1'. This was done because '1' was the value shared by both copies of the same table tables.

That's it! You have successfully retrieved data using 'SELF JOIN' statement.

Frequently Asked Questions :

A self join in SQL is when a table is joined with itself.

The difference between inner join and self join in SQL is that inner join involves joining two separate tables, while self join involves joining a table with itself.

An outer join in SQL combines rows from two tables even if there is no match, while a self join involves joining a table with itself.

Self join in SQL involves joining a table with itself, while a Cartesian join combines every row from one table with every row from another table.