8. Inner and Outer Joins: Step 6

Demo

Show -ALL- Department locations with their employees

The OUTER Join includes all rows in one table even if they have no matching values in the other. Either the LEFT of the RIGHT (or both) tables can be included in full. Typically we put the table to be shown in full on the LEFT

SQL

SELECT location, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;

Output

location ename
New York CLARK
New York KING
New York MILLER
Dallas SMITH
Dallas JONES
Dallas SCOTT
Dallas ADAMS
Dallas FORD
Chicago ALLEN
Chicago WARD
Chicago MARTIN
Chicago BLAKE
Chicago TURNER
Chicago JAMES
Boston

Discussion

Boston is now included in the output, with the ename field NULL. Outer joins are often needed when workng with unknown data.