SQL-92 Join Syntax

SQL-92 provides some keywords for joins (JOIN, CROSS JOIN, and NATURAL JOIN, which can be combined with INNER, RIGHT OUTER, LEFT OUTER, and FULL OUTER to make the full spectrum of joins). Some or all of these keywords are implemented by an increasing number of systems, including the Adaptive Server Anywhere included with this book. However, the original FROM/WHERE join syntax is still common, and vendors that support the new JOIN keyword syntax also provide it. For this reason, most of the examples in this chapter use the original FROM/WHERE syntax. Examples of the new syntax are provided as translations—the concepts are not different, only the annotation is.

Following are some ANSI syntax alternatives. The FROM clause includes the keyword JOIN. The join columns are listed in a special ON or USING clause. (Our demo database, ASA, supports ON.)

SELECT select_list
FROM table_1 JOIN table_2
[ON [table_1.]column  join_operator  [table_2.]column
   | USING (column) ]

The join operator can be the JOIN keyword alone or can be modified by CROSS or NATURAL. When it is, the ON and USING keywords are not allowed.

• A CROSS JOIN uses the WHERE clause to qualify joining columns (if you leave off the WHERE clause, it produces a Cartesian product). For more information see “Avoiding a Common Source of Errors” in this chapter.

• A NATURAL JOIN works only when the two tables have a single identical column. It assumes the join is on this column, so you don't specify it in the ON, USING, or WHERE clause.

Following are examples of three JOIN keyword variants and their corresponding join column notation (in this case they all produce the same results). The comments (preceded by double dashes) at the beginning of each piece of code mark the elements to look at.

--JOIN keyword and ON clause
select ed_lname, ed_fname, ed_pos
from editors join titleditors
on editors.ed_id = titleditors.ed_id
where titleditors.title_id = 'PC8888'

--CROSS JOIN keyword and WHERE clause
select ed_lname, ed_fname, ed_pos
from editors cross join titleditors
where editors.ed_id = titleditors.ed_id
   and titleditors.title_id = 'PC8888'

--NATURAL JOIN keyword, no join columns specified in WHERE clause
select ed_lname, ed_fname, ed_pos
from editors natural join titleditors
where titleditors.title_id = 'PC8888'
ed_lname                             ed_fname             ed_pos
==================================== ==================== ===========
DeLongue                             Martinella           project
Samuelson                            Bernard              project
Kaspchek                             Christof             acquisition
[3 rows]

Not all systems support every element of the ANSI join keyword syntax (JOIN, CROSS, and NATURAL). When you specify a join, whether original or ANSI, you must list multiple tables and indicate which columns you are joining (Figure 7.2).