SQL JOIN Clauses

There are three types of JOIN clauses that can be used in the FROM clause to perform relational joins between source tables. The implicit join condition is always Cartesian for source tables without an explicit JOIN clause. The following table outlines the three types of JOIN clauses that can be used in the FROM clause. See below for more detailed information about each type of join.

Cartesian Join

A Cartesian join connects two tables in a non-relational manner. The syntax is as follows:

  FROM table_reference, table_reference [,table_reference...]

Use a Cartesian join to connect the field of two tables into one result set, but without correlation between the rows from the tables. Cartesian joins match each line of the source table with each line of the joining table. No field comparisons are used, just simple association. If the source table has 10 rows and the joining table has 10, the result set will contain 100 rows as each line from the source table is joined with each line from the joined table.

INNER JOIN Clause

An INNER JOIN connects two tables based on field values common between the two, excluding non-matches. The syntax is as follows:

  FROM table_reference [INNER] JOIN table_reference ON predicate [[INNER] JOIN table_reference ON predicate...]

Use an INNER JOIN to connect two tables, a source and joining table, that have values from one or more fields in common. One or more fields from each are compared in the ON clause for equal values. For rows in the source table that have a match in the joining table, the data for the source table rows and matching joining table rows are included in the result set. Rows in the source table without matches in the joining table are excluded from the joined result set. In the following example the AR Customer ARCUST and IN Invoice Header INHEAD tables are joined based on values in the CustomerCode field:

  SELECT * FROM ARCUST INNER JOIN INHEAD ON ARCUST.CustomerCode = INHEAD.CustomerCode

More than one table can be joined with an INNER JOIN. The INNER JOIN operator and corresponding ON clause can be repeated for each set of two tables joined. One fields comparison predicate in an ON clause is required for each field compared to join each two tables. The following example joins the ARCUST table to INHEAD, and then INHEAD to INLINE (IN Invoice Lines). In this case, the joining table INHEAD acts as a source table for the joining table Items. Note that the statement appears without the optional INNER keyword.

  SELECT * FROM ARCUST JOIN INHEAD ON (ARCUST.CustomerCode = INHEAD.CustomerCode) JOIN INLINE ON (INHEAD.DocumentID = INLINE.DocumentID)

Tables can also be joined using a concatenation of multiple field values to produce a single value for the join comparison predicate. In the following example the Category1 and Category2 fields in INHEAD are concatenated and compared with the single Comment field in ARCUST:

  SELECT * FROM ARCUST INNER JOIN INHEAD ON (ARCUST.Comment = INHEAD.CATEGORY1 || INHEAD.CATEGORY2)

OUTER JOIN Clause

The OUTER JOIN clause connects two tables based on field values common between the two, including non- matches. The syntax is as follows:

  FROM table_reference LEFT | RIGHT | FULL [OUTER] JOIN table_reference ON predicate [LEFT | RIGHT [OUTER] JOIN table_reference ON predicate...]

Use an OUTER JOIN to connect two tables, a source and joining table, that have one or more fields in common. One or more fields from each are compared in the ON clause for equal values. The primary difference between inner and outer joins is that in OUTER joins rows from the source table that do not have a match in the joining table are not excluded from the result set. Fields from the joining table for rows in the source table without matches have NULL values.

In the following example the ARCUST and INHEAD tables are joined based on values in the CustomerCode field. For rows from ARCUST that do not have a matching value between ARCUST.CustomerCode and INHEAD.CustomerCode, the fields from INHEAD contain NULL values:

  SELECT * FROM ARCUST LEFT OUTER JOIN INHEAD ON (ARCUST.CustomerCode = INHEAD.CustomerCode)

The LEFT modifier causes all rows from the table on the left of the OUTER JOIN operator to be included in the result set, with or without matches in the table to the right. If there is no matching line from the table on the right, its fields contain NULL values. The RIGHT modifier causes all rows from the table on the right of the OUTER JOIN operator to be included in the result set, with or without matches. If there is no matching line from the table on the left, its fields contain NULL values. The FULL modifier returns non-matching rows from both left and right, that is, a combination of both LEFT and RIGHT.

More than one table can be joined with an OUTER JOIN. The OUTER JOIN operator and corresponding ON clause can be repeated for each set of two tables joined. One field comparison predicate in an ON clause is required for each field compared to join each two tables. The following example joins the ARCUST table to the INHEAD table, and then INHEAD to INLINE. In this case, the joining table INHEAD acts as a source table for the joining table Items:

  SELECT * FROM ARCUST LEFT OUTER JOIN INHEAD ON (ARCUST.CustomerCode = INHEAD.CustomerCode) LEFT OUTER JOIN INLINE ON (INHEAD.DocumentID = INLINE.DocumentID)

Tables can also be joined using expressions to produce a single value for the join comparison predicate. In the following example the Category1 and Category2 fields in INHEAD are separately compared with two values produced by the SUBSTRING function using the single Comment field in ARCUST:

  SELECT * FROM ARCUST RIGHT OUTER JOIN INHEAD ON (SUBSTRING(ARCUST.Comment FROM 1 FOR 2) = INHEAD.CATEGORY1 AND SUBSTRING(ARCUST.COMMENT FROM 3 FOR 1) = INHEAD.CATEGORY2)