It is where a table is joined to itself assuming the tables are 2 tables with temporarily renaming one table in SQL query.
For example EMPLOYEE Table has
EMP_ID | EMP_NAME | HIRE_DATE | SALARY | DEPT_ID |
---|---|---|---|---|
1 | Roger Fed | 2001-05-01 | 5000 | 4 |
2 | Djoker Nova | 2002-07-15 | 6500 | 1 |
3 | Stef Graph | 2005-10-18 | 8000 | 5 |
4 | Mart Hingis | 2007-01-03 | 7200 | 3 |
5 | Andre Duplesi | 2008-06-24 | 5600 | null |
Now lets write SELF JOIN query:
SELECT , E.EMP_NAME, E.SALARY , F.HIRE_DATE
FROM EMPLOYEE E, EMPLOYEE F
WHERE E.SALARY < F.SALARY;
The Output will look like this:
emp_name | salary | hire_date |
---|---|---|
Roger Fed | 5000 | 2002-07-15 |
Roger Fed | 5000 | 2005-10-18 |
Roger Fed | 5000 | 2007-01-03 |
Roger Fed | 5000 | 2008-06-24 |
Roger Fed | 5000 | 2009-04-01 |
Roger Fed | 5000 | 2011-04-13 |
Djoker Nova | 6500 | 2005-10-18 |
Djoker Nova | 6500 | 2007-01-03 |
Mart Hingis | 7200 | 2005-10-18 |
Andre Duplesi | 5600 | 2002-07-15 |
Learn more : SQL Tutorial , SQL Interview Questions