# JOIN operations

The JOIN operations perform joins between two tables.

The JOIN operations are among the possible tableExpressions in a FROM clause. (You can also perform a join between two tables using an explicit equality test in a WHERE clause, such as `WHERE t1.col1 = t2.col2`.)

The JOIN operations are:

- INNER JOIN operation

  Specifies a join between two tables with an explicit join clause.

- LEFT OUTER JOIN operation

  Specifies a join between two tables with an explicit join clause, preserving unmatched rows from the first table.

- RIGHT OUTER JOIN operation

  Specifies a join between two tables with an explicit join clause, preserving unmatched rows from the second table.

- CROSS JOIN operation

  Specifies a join that produces the Cartesian product of two tables. It has no explicit join clause.

- NATURAL JOIN operation

  Specifies an inner or outer join between two tables. It has no explicit join clause. Instead, one is created implicitly using the common columns from the two tables.

In all cases, you can specify additional restrictions on one or both of the tables being joined in outer join clauses or in the WHERE clause.

## JOIN expressions and query optimization

For information on which types of joins are optimized, see Tuning Derby.

- INNER JOIN operation
  An INNER JOIN is a JOIN operation that allows you to specify an explicit join clause.
- LEFT OUTER JOIN operation
  A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
- RIGHT OUTER JOIN operation
  A RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table.
- CROSS JOIN operation
  A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may, however, specify a WHERE clause in the SELECT statement.
- NATURAL JOIN operation
  A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

Parent topic: SQL language reference
Related concepts
SQL identifiers
Statements
Built-in system functions
Built-in system procedures
Data types
Related reference
Capitalization and special characters
SQL clauses
SQL expressions
SQL queries
Built-in functions
SYSCS_DIAG diagnostic tables and functions