SQL LEFT OUTER JOIN

The result of a left outer join (or simply left join) for table A and B always contains records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result-but with NULL in each column from B.

This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

For example, this allows us to find an employee’s department, but still shows the employee(s) even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments are excluded from the result).

SQL> SELECT e.ename. d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);