SQL2 Joins

The SQL2 standard introduced more precise methods of specifying how tables are to be joined. There are numerous possibilities, including a number of rarely used options for which there are simpler alternatives. The DP4 SQL run-time supports most of the useful SQL2 join constructions. For a more in depth explanatation of the join concept refer to SQL Joins, which is a revised version of a DP4 User group article.

Both left and right outer joins are supported. For example, to show customers who have not been allocated to regions (as well as those who have):

SELECT customer.name, region.name
FROM customer LEFT OUTER JOIN region
ON customer.region_code=region.region_code

To show the reverse (regions with no customers as well as with) :

SELECT region.name, customer.name
FROM customer RIGHT OUTER JOIN region
ON customer.region_code=region.region_code

The word LEFT can be omitted. Alternatively the word OUTER can be omitted if either LEFT or RIGHT is specified. The word FULL is also accepted instead of LEFT or RIGHT. However FULL outer joins are not implemented correctly - the LEFT outer join is returned instead. The order of comparison in the ON clause makes no difference to the results of the query.

DP4 does not support SQL2 NATURAL joins. ON clauses must be used instead. Neither UNION nor CROSS joins are permitted. In the latter case simply listing the tables without using a JOIN or a WHERE clause is equivalent.

DP4 supports multiple levels of join. You should parenthesise nested join expressions to clarify the intended order of execution. In some cases the DP4 SQL Engine may generate an internal error on unparenthesised nested joins. Apart from this minor restriction, any mixture of left and right outer joins is accepted (but see below for a general comment on right joins). It should be noted that using outer joins may force DP4 to access the database in a less efficient manner. In the example above, using a right outer join will force DP4 to read the region table first and then to read the customer table to find the customers for each region. If the region index for the customer table is not set to update, this means the whole customer table may need to be read for each region. In the above examples the LEFT outer join is not inefficient - in DP4 terms it allows a record with a "missing parent" to be listed normally. You must bear in mind that in SQL customer LEFT JOIN region and region RIGHT JOIN customer are more or less equivalent, so it is not always the case that left joins are efficient and right joins are not. You must consider the structure of the database.

Although right joins are accepted, and the correct results are produced for them, internally they are processed by the DP4 SQL Engine transforming them into the equivalent left join expression. In rare cases where there are multiple levels of join this may cause an internal error. Such an error is not possible with a left join.

Alternate Syntaxes

The following alternative syntaxes adopted from ODBC and other SQL database implementations are also accepted:

SELECT region.name, customer.name
FROM {OJ customer LEFT OUTER JOIN region
ON region.region_code=customer.region_code}

SELECT region.name, customer.name
FROM customer, region
WHERE customer.region_code = region.cd_region (+)

The former syntax is the SQL2 syntax but enclosed in an ODBC escape. In the latter notation, adopted from Oracle and used by some versions of Crystal Reports, (+) implies that the relevant table is to have "imaginary rows" added, (with null columns), to ensure that every item in the first table can be listed. Typically the (+) symbol will appear on the opposite side of the WHERE clause (i.e. it is on the right for a left outer join and vice-versa.)

You are strongly recommended not to use these alternate means of specifying outer joins, especially the latter (which cannot be used to specify multiple outer joins in a non-ambiguous way). The first documented syntax adheres to the SQL2 standard.