Progress
SQL-92
Guide and Reference
Outer Joins
An outer join between two tables returns more information than a corresponding inner join. An outer join returns a result table that contains all the rows from one of the tables even if there is no row in the other table that satisfies the join condition.
In a left outer join, the information from the table on the left is preserved: the result table contains all rows from the left table even if some rows do not have matching rows in the right table. Where there are no matching rows in the right table, SQL generates null values.
In a right outer join, the information from the table on the right is preserved. The result table contains all rows from the right table even if some rows do not have matching rows in the left table. Where there are no matching rows in the left table, SQL generates null values.
SQL uses two forms of syntax to support outer joins:
- In the WHERE clause of a query expression, specify the outer join operator ( + ) after the column name of the table for which rows will not be preserved in the result table. Both sides of an outer join search condition in a WHERE clause must be simple column references. This syntax allows both left and right outer joins.
NOTE: The Progress implementation of syntax for awhere_clause_outer_join
does not comply with the ANSI SQL-92 standard. The alternate syntax will not operate properly for clients requiring SQL-92 standard syntax.
- For left outer joins only, in the FROM clause, specify the LEFT OUTER JOIN clause between two table names, followed by a search condition. The search condition can contain only the join condition between the specified tables.
- Full (two-sided) outer joins are not supported.
- Right outer joins are only supported using the outer join operator in the WHERE clause. The keywords RIGHT OUTER JOIN are not supported currently.
This is the syntax for a from_clause_outer_join:
This is the syntax for a where_clause_outer_join:
EXAMPLE
The following example shows a left outer join. The query requests information about all the customers and their orders. Even if there is not a corresponding row in the orders table for each row in the customer table, NULL values are displayed for the order.order_no and order.order_date columns:
The following queries illustrate outer join syntax:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |