JOIN Predicate (join_predicate)

A JOIN predicate (join_predicate) is a predicate that specifies a join. A join is an SQL statement that links several tables with one another and returns a join table as its result. A JOIN predicate can be specified with or without an OUTER JOIN indicator.

Structure

Syntax Syntax

<join_predicate> ::=
  <expression> [<outer_join_indicator>]
    <comp_op> <expression> [<outer_join_indicator>]

<outer_join_indicator> ::=
  (+) <!  This SQL clause is
          no longer recommended to be used
          and might be removed from future versions.  !>

<comp_op> ::=
  <
| >
| <>
| !=
| =
| <=
| >=
| ~= <!  for computers with ASCII code  !>
| ~< <!  for computers with ASCII code  !>
| ~> <!  for computers with ASCII code  !>

End of the code.

Examples

SQL Tutorial, Joins: Information From Several Tables

Explanation

The JOIN predicate is a special type of comparison predicate. The rules for a comparison predicate apply here.

Each expression (expression) specified in the JOIN predicate must contain a column specification. The column specification of the first expression and the column specification of the second expression must be specified in such a way that both column specifications refer to different table names or reference names. The value of the first expression and the value of the second expression must be comparable.

Only those rows from the table specified in the JOIN predicate are transferred to the result table for which a row is found in the other table specified in the JOIN predicate in accordance with the comparison (specified by the comparison operator comp_op).

OUTER JOIN Indicator

Caution Caution

This SQL clause is no longer recommended to be used and might be removed from future versions.

End of the caution.

Only one OUTER JOIN indicator (outer_join_indicator) may be specified in a JOIN predicate. If each row in a table (table A) specified in the JOIN predicate is to appear at least once in the result table, the OUTER JOIN indicator must be specified on the side of the comparison operator where the other table (table B) is specified in the JOIN predicate. Even if at least one counterpart is not found in table B for a row in table A, the row in table A is still transferred to the result table. The NULL value is then used for the output columns that are formed from the columns in table B.

More Information

Joined Table (joined_table)

Join View Table in an INSERT Statement

Updatable Join View Table

CREATE VIEW Statement

Search Requirements

Restrictions for SQL Statements

Concepts of the Database System, search strategies for Joins, EXPLAIN Statement for Joins