Outer Joins in DBMS

These operations are the modified versions of normal joins. It uses best suited normal join algorithms first, then it picks the records of current table which are not participated in join and adds them to result set by adding NULLs to the other table columns. It can use hash join or merge join to perform equijoin in first step.

Let us have query like below:

SELECT * FROM EMP e LEFT OUTER JOIN DEPT d ON e.DEPT_ID = d.DEPT_ID;

The processer picks all matching records of EMP and DEPT by using the best join algorithm described above, then it picks the records of EMP which are not selected by first step above and adds them to the result set by appending NULLs to the columns of DEPT.

Translate ยป