Progress
SQL-89
Guide and Reference
Choosing Explicit Joins or Implicit Joins
Explicit joins provide a greater variety of join operations than implicit joins. However, there are some trade offs and restrictions when using them.
Join Conditions and Selection Criteria
In the previous examples of explicit joins, the ON clause specifies both table join conditions (Customer.Sales-Rep = Salesrep.Sales-Rep) and selection criteria for individual tables participating in the join (Customer.Balance > 50000). For explicit joins, you must specify the join conditions using the ON clause, but you can alternatively specify the table selection criteria using the WHERE clause of the SELECT statement, just like implicit joins.
However, there is a difference in the way the ON and WHERE clauses apply selection criteria in an explicit join. The ON clause applies the selection criteria as the join is created and the WHERE clause applies the selection criteria after the join is created.
This difference can have two areas of performance impact:
- Network traffic — The ON clause is applied on the server whenever possible. This means that in a networked client/server application, the ON clause can return the selected join(s) to the client, causing less data to be returned over the network. This increases throughput, especially using high-performance servers.
- Selection volume — The WHERE clause must apply all of its selection criteria to the join that results from the full combination of tables in the SELECT. If you do not use the ON clause for selection, not only is more data returned to the client from the server, but the unselected join result can contain more records for the WHERE clause to select. For example, the same customer record might appear more than once in a multiple join, each instance of which must be selected by a WHERE clause. The ON clause, on the other hand, applies the selection criteria only to the records returned for a single join combination. Thus, for example, a single customer record can be selected once and eliminated from one join before it is combined with additional records from subsequent joins.
Thus, explicit joins afford maximum performance benefits wherever they can be used.
Restrictions on Explicit Joins
In Progress/SQL, explicit joins have the following restrictions:
However, where you are unable to use an explicit join, you can use an implicit join.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |