SQL SELF JOIN in DB2 for i SQL

SQL SELF JOIN in DB2 for i SQL

Self Join

A self join is a regular join, but the table is joined with itself.

Syntax

SELECT column_name(s)
FROM table1 a, table1 b
WHERE condition

Here, a and b are different aliases name given to the table1.

Or

SELECT column_name(s)
FROM table1 AS alias1 JOIN table1 as alias2
ON alias1.column = alias2.column

Create Tables for understanding SELF JOIN

Create Employee Table

Create table query for EMPLOYEE table:

CREATE TABLE EMPLOYEE(EMPID INT NOT NULL WITH DEFAULT, EMPNAME    
CHAR(20) NOT NULL WITH DEFAULT, MANAGERID INT                     
, CONSTRAINT UNIQUEID4 UNIQUE (EMPID))                            

Insert data query

INSERT INTO EMPLOYEE(EMPID, EMPNAME, MANAGERID) 
VALUES                                          
(1, 'JOHN', 5),                                 
(2, 'JANARDAN', 5),                             
(3, 'RAJ', 2),                                  
(4, 'ABHISHEK', 2),                             
(5, 'SATISH', NULL)                             

Select data from employee table and display

SELECT * from employee

EMPID   EMPNAME                   MANAGERID 
    1   JOHN                              5 
    2   JANARDAN                          5 
    3   RAJ                               2 
    4   ABHISHEK                          2 
    5   SATISH                             -

Example using SQL SELF JOIN

Perform SELF JOIN on Table employee to list out employees and their managers

SELECT e.empname AS Employee,    
m.empname AS Manager FROM        
employee AS e JOIN employee AS m 
ON e.managerid = m.empid         

Or

SELECT e.empname AS Employee, 
m.empname AS Manager FROM     
employee AS e, employee AS m  
where e.managerid = m.empid   

We did performed inner join on employee table itself by providing it alias name and joining the same file based on manager id and empid on the alias name table e and m and selected the employee name and their respective manager name.

Result:

EMPLOYEE              MANAGER   
JOHN                  SATISH    
JANARDAN              SATISH    
RAJ                   JANARDAN  
ABHISHEK              JANARDAN