Suppose we have more than two tables involved in a query. Then how to evaluate the query?
Suppose we have to get the employee details, his department details and his project details. Then the query would be as below
SELECT e.EMP_ID, e.EMP_NAME, d.DEPT_ID, d.DEPT_NAME, p.PROJ_ID, p.PROJ_NAME FROM EMP e, DEPT d, PROJ p WHERE e.DEPT_ID = d.DEPT_ID AND e.PROJ_ID = p.PROJ_ID;
Above query can be processed in three steps:
- Join the EMP and DEPT to get the result. Use this result to join with PROJ to get the final result.
- Join EMP and PROJ to get the result. Use this result to join with DEPT to get the final result.
- Create index on DEPT_ID of DEPT, and PROJ_ID of PROJ. Now each record in EMP will fetch the matching record in DEPT and PROJ in one shot, rather than traversing each record of tables. This method performs pair join rather than joining with one table at a time. This method is more efficient than other two methods because it does not traverse each record of tables every time. All matching records are fetched in one step.