Progress
SQL-89
Guide and Reference
Specifying Explicit Joins
The following syntax of the FROM clause specifies an explicit join.
An explicit join is a join specification in which the type of join is specified by JOIN and its associated options, and the join conditions are specified by an ON clause. The ON clause can specify one or more join conditions connected by the logical operators AND, OR, and NOT.
You can also specify table selection criteria either with the ON clause or the WHERE clause of the SELECT statement. However, the ON clause offers better performance, because its search conditions can often be satisfied on the server. By default, the JOIN option specifies an inner join between the right table and the left table (or join). For more information, see the "Choosing Explicit Joins or Implicit Joins" section.
Inner and Left Outer Joins
You can mix inner and left outer joins, joining as many tables as you want. You cannot use parentheses or otherwise nest explicit joins. Multiple explicit joins always proceed in series from left to right, similar to implicit joins. Each successive inner or left outer join joins the next table on the right with the previous join.
The most efficient mix of inner and left outer joins specifies inner joins all together in one series on the left, and left outer joins all together in a second series on the right. This is because the results of any left outer joins followed by an inner join are identical to the results of all inner joins on the same tables. Specifying left outer joins prior to an inner join only makes the join process more lengthy because the final inner join must eliminate all joins containing null records that are generated by prior left outer joins.
Examples
The following example shows an explicit inner join of the order table and the order-line table, and returns only those rows in the order table that have a row in the order-line table with the same order-num.
This example shows an inner join of four tables: customer, order, order-line, and item. It displays only those orders that contain line items with quantity sales extended to greater than two-thirds the customer credit limit.
This example shows a left outer join of the same four tables, which in addition to the inner join displays customers without orders (returning null order records) and all orders without sufficiently extended order lines (returning null order-line and item records).
This example shows an inner join of the customer and order tables followed by left outer joins of the order-line and item tables. The initial inner join ignores customers without orders (returning only valid orders), and the outer joins show all orders without sufficiently extended order lines (returning null order-line and item records).
Right Outer Joins
In Progress/SQL you currently can specify a right outer join between no more than two tables in a single SELECT statement. This limitation originates in the implementation of right outer joins, which are based on reversed left outer joins. To properly associate multiple right outer joins using the current left outer join implementation, the Progress/SQL Compiler would have to allow nested joins, which it does not. Thus, you can combine only two tables at a time with a right outer join.
This example shows a right outer join of the Customer and Salesrep tables based on current customer balances. Thus, it shows sales reps and their customers for customers with balances greater than $50,000 and returns null customer records for sales reps that have no customers with balances that high.
This query demonstrates the Customer and Salesrep tables “right outer joined” on current balance. The query returns salesreps and, for each salesrep, customers with balances greater than $50,000. For salesreps with no customers with balances greater than $50,000, the query returns the salesrep and the “null” customer.
Explicit Self-Joins
Like implicit joins, you can specify a self-join in an explicit join using a correlation name for the same table. This example lists customers on the left paired with all other customers with the same sales rep on the right that have a lower balance than the customer on the left.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |