Written by SQL Self Join
A self join in SQL is a way to join a table with itself. It allows you to retrieve data by creating a temporary copy of a table and then joining it with the original table based on certain conditions.To perform a self join, you typically need to use table aliases to differentiate between the copies of the table. Here’s a general syntax for a self join:
sql
SELECT column1, column2, ... FROM table1 AS t1, table1 AS t2 WHERE t1.columnX = t2.columnY; table1 is the original table, and we create two temporary copies of it using table aliases t1 and t2. We then join these two copies based on the condition t1.columnX = t2.columnY. You can replace columnX and columnY with the specific columns you want to compare.Here’s a more concrete example to illustrate a self join. Let’s say we have a table called employees with the following structure:sql
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), manager_id INT ); manager_id column refers to the id column of the same table, indicating the manager of each employee. To retrieve the name of each employee and their corresponding manager’s name, you can use a self join:sql
SELECT e.name AS employee_name, m.name AS manager_name FROM employees AS e, employees AS m WHERE e.manager_id = m.id; In this example, we use table aliases e and m to differentiate between the employee and manager copies. We join the copies based on the condition e.manager_id = m.id to match each employee with their respective manager. The result will contain two columns: employee_name and manager_name.Self joins can be useful in scenarios where you need to compare records within the same table, such as hierarchical relationships or tracking dependencies.
