Join Expressions

On this page

Join expressions, also called "joins", combine the results of two or more table expressions based on conditions on the values of particular columns.

Join expressions define a data source in the FROM sub-clause of simple SELECT clauses, or as parameter to TABLE. Joins are a particular kind of table expression.

Synopsis

Parameters

Parameter	Description
`joined_table`	Another join expression.
`table_ref`	A table expression.
`a_expr`	A scalar expression to use as `ON` join condition.
`name`	A column name to use as `USING` join condition

Supported Join Types

CockroachDB supports the following uses of JOIN.

Inner Joins

Only the rows from the left and right operand that match the condition are returned.

<table expr> [ INNER ] JOIN <table expr> ON <val expr>
<table expr> [ INNER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL [ INNER ] JOIN <table expr>
<table expr> CROSS JOIN <table expr>

Left Outer Joins

For every left row where there is no match on the right, NULL values are returned for the columns on the right.

<table expr> LEFT [ OUTER ] JOIN <table expr> ON <val expr>
<table expr> LEFT [ OUTER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL LEFT [ OUTER ] JOIN <table expr>

Right Outer Joins

For every right row where there is no match on the left, NULL values are returned for the columns on the left.

<table expr> RIGHT [ OUTER ] JOIN <table expr> ON <val expr>
<table expr> RIGHT [ OUTER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL RIGHT [ OUTER ] JOIN <table expr>

Full Outer Joins

For every row on one side of the join where there is no match on the other side, NULL values are returned for the columns on the non-matching side.

<table expr> FULL [ OUTER ] JOIN <table expr> ON <val expr>
<table expr> FULL [ OUTER ] JOIN <table expr> USING(<colname>, <colname>, ...)
<table expr> NATURAL FULL [ OUTER ] JOIN <table expr>

Supported Join Conditions

CockroachDB supports the following conditions to match rows in a join:

No condition with CROSS JOIN: each row on the left is considered to match every row on the right.
ON predicates: a Boolean scalar expression is evaluated to determine whether the operand rows match.
USING: the named columns are compared pairwise from the left and right rows; left and right rows are considered to match if the columns are equal pairwise.
NATURAL: generates an implicit USING condition using all the column names that are present in both the left and right table expressions.

Warning:

NATURAL is supported for compatibility with PostgreSQL; its use in new applications is discouraged, because its results can silently change in unpredictable ways when new columns are added to one of the join operands.

Performance Best Practices

Note:

CockroachDBs is currently undergoing major changes to evolve and improve the performance of queries using joins. The restrictions and workarounds listed in this section will be lifted or made unnecessary over time.

Joins over interleaved tables are usually (but not always) processed more effectively than over non-interleaved tables.
When no indexes can be used to satisfy a join, CockroachDB may load all the rows in memory that satisfy the condition one of the join operands before starting to return result rows. This may cause joins to fail if the join condition or other WHERE clauses are insufficiently selective.
Outer joins are generally processed less efficiently than inner joins. Prefer using inner joins whenever possible. Full outer joins are the least optimized.
Use EXPLAIN over queries containing joins to verify that indexes are used.
See Index Best Practices.

Pricing

Contact us

Sign In