Outer Joins in SQL/92 Syntax

SQL/92 syntax for performing outer joins:

FROM table1 { LEFT | RIGHT | FULL } OUTER JOIN table2

A full outer join uses all rows in table1 and table2. A full outer join includes null values in the columns used in the join.

Left Outer Joins Using SQL/92

Query using (+):


SELECT p.name, pt.name
FROM projects p, project_types pt
WHERE p.project_type_id = pt.project_type_id (+)
ORDER BY p.name;

Query using the SQL/92 LEFT OUTER JOIN keywords:


SELECT p.name, pt.name
FROM projects p LEFT OUTER JOIN project_types pt
USING (project_type_id)
ORDER BY p.name;

Query using (+):


SELECT p.name, pt.name 
FROM projects p, project_types pt
WHERE p.project_type_id (+) = pt.project_type_id;

Query using the SQL/92 RIGHT OUTER JOIN keywords:


SELECT p.name, pt.name
FROM projects p RIGHT OUTER JOIN project_types pt
USING (project_type_id)
ORDER BY p.name;

A full outer join uses all rows in the joined tables, including null values in either of the columns used in the join.


SELECT p.name, pt.name
FROM projects p FULL OUTER JOIN project_types pt
USING (project_type_id)
ORDER BY p.name;