SQL - Structured Query Language

Self Join

Self join is used to retrieve list of common columns matched data value records within a table with two different columns. The following self join is used to retrieve list of Employee and their Manager information that matched with Emp_Code and MGR_Code.
Note: For Demo, create EMP_Test Table and then INSERT sample records.

-- Example 37 --

USE EBusiness

CREATE TABLE EMP_Test

(

Emp_Code numeric(5)Primary Key,

Emp_Name varchar(50) Not Null,

MGR_Code numeric(5) References EMP(Emp_Code)

)

INSERT INTO EMP_Test VALUES (101,'Employee 101',NULL)

INSERT INTO EMP_Test VALUES (102,'Employee 102',101)

INSERT INTO EMP_Test VALUES (103,'Employee 103',101)

INSERT INTO EMP_Test VALUES (104,'Employee 104',101)

INSERT INTO EMP_Test VALUES (105,'Employee 105',101)

INSERT INTO EMP_Test VALUES (106,'Employee 106',NULL)

SELECT * FROM EMP_Test

SELECT EMP_Test.Emp_Code, Employee_Name = ET.Emp_Name, Manager_Name = EMP_Test.Emp_Name

FROM EMP_Test

INNER JOIN EMP_Test ET

ON EMP_Test.Emp_Code = ET.MGR_Code

Query Output Screen

* * * * *

Your Comment To AUTHOR