Getting Started › Left Outer Joins › Overview of Joins

Overview of Joins

A join is a query used to return rows that consist of columns selected from more than one table. The combined rows that are returned are selected and joined together by each row of each table being evaluated against join predicates. A new table therefore results from a join.

There are different kinds of joins. Inner joins eliminate from the resulting table the combined rows that do not satisfy evaluation against the join predicates. Therefore, with inner joins if no matching row is found, no rows are returned. Inner joins were supported by CA Datacom/DB in previous versions and continue to be supported (see Joining Tables). Outer joins preserve the rows an inner join would discard by returning those rows with nulls substituted for each column of one of the tables.

The SELECT statement's subselect and select-into syntax uses the FROM clause as an optional choice. As shown in the third of the three following syntax diagrams, JOIN is used in the alternate-join-type segment of the table reference syntax (see the second diagram) in the FROM clause (see the first diagram).

In the following diagrams, while a table reference (table-ref) is shown to be the main component of the FROM clause, it can also be referenced directly from inside the JOIN syntax (third diagram, alternate-join-type syntax).

          ┌─ , ─────────┐
►►─ FROM ─▼─ table-ref ─┴─────────────────────────────────────────────────────►◄

The table-ref shown in the syntax box immediately preceding the following one has syntax as follows:

►►─┬─┬─ table-name ─┬─┬────────────────────┬─┬────────────────────────────────►◄
   │ └─ view-name ──┘ └─ correlation-name ─┘ │
   └─ alternate-join-type ───────────────────┘

The alternate-join-type shown in the syntax box immediately preceding the following one has syntax as follows:

►►─┬─────┬─ table-ref ─┬─ INNER ────────────┬─────────────────────────────────►
   └─ ( ─┘             └─ LEFT ─┬─────────┬─┘
                                └─ OUTER ─┘

 ►─ JOINtable-ref ─┬────────────┬─┬─────┬─────────────────────────────────────►◄
                   └─ ONs-cond ─┘ └─ ) ─┘

Note: The s-cond (search-condition) specified in the optional ON clause differs from the one in the WHERE clause in that the ON clause defines the join conditions that determine which rows contain nulls, as opposed to the WHERE clause, which eliminates rows from the result entirely. Also note that if you use the optional parentheses, they must be balanced. That is, if you use an open parenthesis, you must also use a close parenthesis.

The previously shown JOIN syntax is compatible with Ingres, DB2, and ANSI SQL3 Core SQL.