TRAINING (SQL-8) - Data Query Language (DQL) - SQL Joins - Understandding SELF JOIN

SELF JOIN

• Joining one table with the same tables is called SELF JOIN.
• The self join can be viewed as a join of two copies of the same table ( though tables are not actually copied).
• Since both the tables have same name, to distinguish the columns, table aliases are used.
• To achieve the the self join from a single table there should be a logical primary & foreign key mapping must exist i.e. for one column value there should have many column values. (No need to have realy primary/foreign key defined at physical level)
• For example in our exercise we have used emp table that came bundled with an Oracle database in scott schema where:
• From Worker to Manager relationship
• 1 worker (employee) can have 0 or 1 Manager
• From Manager to Worker relationship
• 1 Manager can have 0, 1 or Many Worker (employee)
  
• First in SQL we create virtual copies of table emp named worker, manager and using alias in FROM clause using aliases and also select the columns from both worker, manager tables prefixing column names with Aliases. Thereafter in WHERE clause we specify to select only those rows where in worker table mgr column value is equal to manager table empno column using WHERE worker.mgr= manager.empno.
• SELECT
  worker.empno as WorkerID, worker.ename as WorkerName, worker.job as JobTitle, 
  manager.ename as ManagerName
  FROM emp worker, emp manager
  WHERE worker.mgr= manager.empno;

• Once the SQL query executed its only display the result which matches the criteria.
  
  
• In the result you could see that empno=7839 ename=KING job=PRESIDENT never comes in final result. That is due to the fact that when inner join condition WHERE worker.mgr= manager.empno is executed empno in LEFT table (worker.mgr) never becomes equal to RIGHT table ( manager.empno) as the value for empno 7839 in MGR column in LEFT table (worker) is null.
• However if you also want to include empno=7839 ename=KING in the end result of the SQL, then you have to use LEFT JOIN to include all the matching column of LEFT & RIGHT table plus all the non-matching column of LEFT table using SQL below.
• SELECT
  worker.empno as WorkerID, worker.ename as WorkerName, worker.job as JobTitle, 
  manager.ename as ManagerName
  FROM emp worker, emp manager
  WHERE worker.mgr= manager.empno (+);