Database: SELECT: Joins - SQL-92 Additions

The following table gives the syntax for SQL-92 joins. These should be used in the FROM clause of the SELECT statement, and are preferred over the use of the older list of tables in the FROM with the join condition specified in the WHERE clause. This omits the UNION JOIN which reportedly no DBMS has implemented because the theoretical completeness doesn't have any practical application.

The A column indicates whether Access supports it, and M indicates whether MySQL supports it. Both of these databases support the SQL1 (FROM ... WHERE ...) form of joins, so omission here is not critical to their functioning.
 A  M FROM clause join
-Y t1 NATURAL [INNER] JOIN t2
YY t1 [INNER] JOIN t2 ON c1=c2
-Y t1 [INNER] JOIN t2 USING (c,...)
-Y t1 NATURAL {FULL | LEFT | RIGHT} [OUTER] JOIN t2
YY t1 {FULL | LEFT | RIGHT} [OUTER] JOIN t2 ON c1=c2
-Y t1 {FULL | LEFT | RIGHT} [OUTER] JOIN t2 USING (c,...)
-Y t1 CROSS JOIN t2

Note: MS Access requires the word "INNER". MySQL in contrast doesn't accept the word "INNER" if the "NATURAL" keyword is included.

Inner and outer joins specify fields whose values must match to be included in the result. There are three equivalent ways to specify these columns.

  1. A NATURAL join uses the column in both tables that has the same name (and type) to perform an equi-join. These fields will be the primary key in one table and a foreign key in the other table. If the names of the fields is different in the two tables, use the ON syntax. This is an equi-join.
  2. The USING clause is followed by a list (altho usually only one) of column names that are the same in both tables. This can also be written as an ON clause where the column name on the left is the same as the column name on the right. This is basically the same as a NATURAL join. It is an equi-join.
  3. The ON clause can be followed by any condition, but equi-joins are so overwhelmingly common that only that option is shown here.
These join expressions will be combined in larger expressions when more than two tables are joined. They may be enclosed in parentheses to control the order of evaluation, which may be important for outer joins.
Notation: UPPERCASE words are SQL keywords. [square brackets] enclose optional elements. ",..." follows an item that can be repeated in a comma-separated list. {curly braces} group elements for use with ",..." or |. | (vertical bar) separates alternatives. Italic words stand for something the user supplies.