Learning of SQL Day 23

 

Day 23: SELF JOIN

Introduction:

• A SELF JOIN is a regular join but the table is joined with itself. This can be useful for comparing rows within the same table.

Key Concepts:

• SELF JOIN: A table is joined with itself.

• Alias: Temporary names for tables to distinguish them in a join.

SQL Commands and Examples:

1. Basic SELF JOIN:

   • Join a table with itself using aliases.

sql

-- Assuming we have an Employees table and we want to find pairs of employees in the same department
SELECT E1.EmployeeID AS Employee1, E1.FirstName AS Employee1Name, E2.EmployeeID AS Employee2, E2.FirstName AS Employee2Name, E1.Department
FROM Employees E1
INNER JOIN Employees E2 ON E1.Department = E2.Department AND E1.EmployeeID < E2.EmployeeID;

2. SELF JOIN to Compare Rows:

   • Compare rows within the same table for specific conditions.

sql

-- Find employees with the same manager (assuming ManagerID is a column in Employees)
SELECT E1.EmployeeID AS Employee1, E1.FirstName AS Employee1Name, E2.EmployeeID AS Employee2, E2.FirstName AS Employee2Name
FROM Employees E1
INNER JOIN Employees E2 ON E1.ManagerID = E2.ManagerID
WHERE E1.EmployeeID != E2.EmployeeID;

Practice Exercise:

1. Use the Employees table with columns EmployeeID, FirstName, LastName, Department, ManagerID.

2. Write a query to self join the Employees table to find pairs of employees in the same department.

3. Write a query to self join the Employees table to find employees with the same manager.

sql

-- Self join Employees table to find pairs of employees in the same department
SELECT E1.EmployeeID AS Employee1, E1.FirstName AS Employee1Name, E2.EmployeeID AS Employee2, E2.FirstName AS Employee2Name, E1.Department
FROM Employees E1
INNER JOIN Employees E2 ON E1.Department = E2.Department AND E1.EmployeeID < E2.EmployeeID;

-- Self join Employees table to find employees with the same manager
SELECT E1.EmployeeID AS Employee1, E1.FirstName AS Employee1Name, E2.EmployeeID AS Employee2, E2.FirstName AS Employee2Name
FROM Employees E1
INNER JOIN Employees E2 ON E1.ManagerID = E2.ManagerID
WHERE E1.EmployeeID != E2.EmployeeID;

Important Tips:

• Always use table aliases when performing a SELF JOIN to distinguish between the different instances of the table.

• Be careful with join conditions to avoid creating a Cartesian product unintentionally.

Understanding SELF JOIN operations allows you to compare rows within the same table and is particularly useful for hierarchical data.