December 15, 2022March 10, 2024Self Join – How to Write Self Join Queries EasilySelf Join: By definition, a self join query is a query in which the table is joined to itself. Self joins are written in cases where there is parent child relationships in the data.Let’s take the classic example of employees table. We have to write a self join query to find out the immediate manager of each employee. Sample data of employees table is shown below:TableName: empTable EMPLOYEEID NAME MANAGERID ------------------------------ 101 Mary 102 102 Ravi NULL 103 Raj 102 104 Pete 103 105 Prasad 103 106 Ben 103The self join query to retrieve the manager of each employee.select EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME from empTable EMP, empTable MANAGER where EMP.manager_id = MANAGER.employee_idWriting Self Join Query EasilyIt is always difficult for new SQL developers to write a self join query. Even the experienced ones struggle a bit while writing the self join query. A Self Join query is as easy as writing a normal join between two tables. Before writing the self join query, take a look at the relation between the employee and manager in the following image. The manager ID column in the employees table refers to the employee ID column.EMPLOYEEIDNAMEMANAGERID101Mary102102RaviNull103Raj102104Pete103105Prasad103106Ben103Let’s imagine that there are two employee tables. Call the first one as EMP and the second one as MANAGER as show in the below image. Draw the relationship between these two tables.Now the self join query becomes joining the data between two tables.First, write a query to join the above two tables asselect EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME from EMP, MANAGER where EMP.manager_id = MANAGER.employee_idNow modify this query to self join as shown below:select EMP.EMPLOYEEID, EMP.NAME. MANAGER.NAME from empTable EMP, empTable MANAGER where EMP.manager_id = MANAGER.employee_id To conclude, self join queries are very easy to write. One has to view the same table as two different tables and draw the relationship between these two tables to write a join query.Like this:Like Loading...Related SQL JoinquerySelfself joinselfjoinSQL