Outer Joins in SQL/92 Syntax

SQL/92 syntax for performing outer joins:

• LEFT means a left outer join.
• RIGHT means a right outer join.
• FULL means a full outer join.

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;

Performing Right 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;

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;

Full Outer Joins Using SQL/92

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;

1. Table Join
2. Using Table name to reference duplicate names
3. Table Alias
4. Cartesian Products
5. Join more than two tables
6. Join Conditions and Join Types
7. Outer Joins
8. Left and Right Outer Joins
9. Outer join Error
10. Self Join
11. Outer Self Join
12. Inner Joins Using SQL/92
13. Joins with USING Keyword
14. Inner Joins with More than Two Tables Using SQL/92
15. Inner Joins on Multiple Columns Using SQL/92
16. Outer Joins in SQL/92 Syntax
17. Self Joins Using SQL/92
18. Cross Joins Using SQL/92