Self Join

In SQL, a self join is a type of join where a table is joined with itself. It is useful when you have a single table that contains related data that you want to compare or analyze.

To perform a self join, you need to use two or more instances of the same table and give each instance a unique alias. The aliases are used to differentiate between the columns of each instance of the table.

Syntax

SELECT column_name(s)
FROM A_table, B_table 
WHERE A_table.column_name = B_table.column_name;

Example

Here’s an example of a self join:

Suppose you have a table called “employees” that contains information about employees, including their name and the name of their manager. You can use a self join to find all employees who have the same manager:

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_name = e2.name;

In this example, we use the aliases “e1” and “e2” to differentiate between the two instances of the “employees” table. We join the table on the “manager_name” column, which is used to connect the employee to their manager.

The result of this query will be a table that shows the name of each employee and the name of their manager.

Self joins can be useful in a variety of situations, including when you need to compare data within the same table or when you need to find relationships between data points. However, it’s important to use aliases to keep track of the different instances of the table, and to be mindful of the performance impact of joining a large table with itself.