In SQL , sometimes we need to join the table to itself in order to fetch some data. We basically use this kind of join when we have all the data present in one table itself however at least one column needs some processing depending on some conditions. In such cases, we generally use Self Join.
In this kind of join, we join the same table to itself by creating different alias name of same table. The general query for self join can be :
SELECT tb1.COL1, tb2.COL2, tb2.COL3 FROM table tb1 , table tb2 WHERE tb1.col1 = tb2.col3;
Suppose we have a table, which is having ZONE_ID and CITY corresponding to that ZONE_ID. We want ZONE_ID, CITY along with the CITY name in next ZONE ID as one row, then we may use SELF JOIN to implement the query.
SELECT TB1.ZONE_ID, TB1.CITY, TB2.CITY FROM OPRT_ZONE TB1 LEFT OUTER JOIN OPRT_ZONE TB2 ON TB1.ZONE_ID + 1 = TB2.ZONE_ID;