What are joins? What are the different types of joins?
• Equi joins • Self joins • Cartesian Products • Inner joins • Outer joins • Left Outer Join A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table. Oracle will return NULL for the columns where no match is found. Example You want to list all the departments in the DEPT table and corresponding employee details. However, you want to also list those departments where there are no employees assigned yet. This can be done as follows SELECT D.DEPTNO, DNAME, ENAME, SAL, HIREDATE FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO; • Right Outer Join A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order. Example The LEFT Outer join query can be re written using RIGHT
