ANSI Outer Join Syntax

The ANSI outer join syntax doesn't use the outer join operator (+) in the join condition; rather, it specifies the join type in the FROM clause. The syntax of ANSI outer join is:

FROM table1 { LEFT | RIGHT | FULL } [OUTER] JOIN table2 The syntax elements are:

table1, table2 specifies the tables on which you are performing the outer join.

        

LEFT Specifies that the results be generated using all rows from table1. For those rows in table1 that don’t have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. This is the equivalent of specifying (+) on the table2 side of the join condition in the traditional syntax.

        

RIGHT Specifies that the results be generated using all rows from table2. For those rows in table2 that don’t have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. This is the equivalent of specifying (+) on the table1 side of the join condition in the traditional syntax.

FULL Specifies that the results be generated using all rows from table1 and table2. For those rows in table1 that don’t have corresponding rows in table2, NULLs are returned in the result set for the table2 columns. Additionally, for those rows in table2 that don’t have corresponding rows in table1, NULLs are returned in the result set for the table1 columns. There is no equivalent in the traditional syntax for a FULL OUTER JOIN.

        

OUTER Specifies that you are performing an OUTER join. This keyword is optional. If you use LEFT, RIGHT, or FULL, Oracle automatically assumes an outer join. The OUTER is for completeness sake, and complements the INNER keyword.

To perform a LEFT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use:

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

 FROM DEPARTMENT D LEFT OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;

To perform a RIGHT OUTER JOIN between the DEPARTMENT and LOCATION tables, you can use:

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D RIGHT OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;

If you want to include the departments without a location, as well as the locations without a department, you need to do a full outer join

SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP

FROM DEPARTMENT D FULL OUTER JOIN LOCATION L ON D.LOCATION_ID = L.LOCATION_ID;