JOIN Syntax

Review the full syntax for SQL joins in MariaDB. This guide details the structure of table references, index hints, and various join types supported in SELECT, UPDATE, and DELETE statements.

For an introduction to joins, see Joining Tables with JOIN Clauses Guide.

Description

MariaDB supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor (<= MariaDB 11.6):
    tbl_name [PARTITION (partition_list)]
        [query_system_time_period_specification] [[AS] alias] [index_hint_list]
  | table_subquery [query_system_time_period_specification] [AS] alias
  | ( table_references )
  | { ON table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

table_factor (>= MariaDB 11.7):
    tbl_name [PARTITION (partition_list)]
        [query_system_time_period_specification] [[AS] alias] [index_hint_list]
  | table_subquery [query_system_time_period_specification] [AS] alias [(column_name_list)] 
  | ( table_references )
  | { ON table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

query_system_time_period_specification:
    FOR SYSTEM_TIME AS OF point_in_time
  | FOR SYSTEM_TIME BETWEEN point_in_time AND point_in_time
  | FOR SYSTEM_TIME FROM point_in_time TO point_in_time
  | FOR SYSTEM_TIME ALL

point_in_time:
    [TIMESTAMP] expression
  | TRANSACTION expression

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

A table reference is also known as a join expression.

Each table can also be specified as db_name.tabl_name. This allows to write queries which involve multiple databases. See Identifier Qualifiers for syntax details.

The syntax of table_factor is an extension to the SQL Standard. The latter accepts only table_reference, not a list of them inside a pair of parentheses.

This is a conservative extension if we consider each comma in a list of table_reference items as equivalent to an inner join. Consider this query:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

It is equivalent to this query:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

In MariaDB, CROSS JOIN is a syntactic equivalent toINNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with anON clause, CROSS JOIN is used otherwise.

In general, parentheses can be ignored in join expressions containing only inner join operations. MariaDB also supports nested joins (see Nested Join Optimization).

Subqueries

A table subquery is specified as a parenthesized query and must contain a following derived table name (specified as alias in the above syntax specification).

Current

You can optionally specify a list of column names in parenthesis.

SELECT ic1, ic2, ic3 FROM
      (
        SELECT c1, c2, c3 FROM t1 GROUP BY c1
      ) dt2 (ic1, ic2, ic3)
    JOIN t2 ON t2.c1 = dt2.ic1
    WHERE c2 > 0
    GROUP BY ic1

Here, the table subquery for t1 will be materialized and named dt2, with column names ic1, ic2, ic3. These column names are used outside the subquery.

< 11.7

See also Correlation Column List.

System-Versioned Tabled

See System-versioned tables for more information about the FOR SYSTEM_TIME syntax.

Index Hints

Index hints can be specified to affect how the MariaDB optimizer makes use of indexes. For more information, see How to force query plans.

Oracle Mode

This feature is available from MariaDB 12.1.

Overview

When Oracle mode is active, the Oracle-style + syntax can be used. For example, the following two queries are identical:

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;

and

SELECT * FROM t1, t2 WHERE t1.a = t2.b(+);

Similarly, the following two queries are identical:

SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;

and

SELECT * FROM t1, t2 WHERE t1.a(+) = t2.b;

Caveats

Outer join operators

Outer join operators can occur only in the WHERE clause. The WHERE clause can consist of one predicate, or of multiple predicates connected with AND. Each of the predicates can reference:

• only one outer-joined table (that is, the "INNER" table) (all references to its columns have an "outer join operator");

• zero, one, or more "OUTER" tables (without an outer join operator).

If a query uses outer join operators, the FROM clause must be a simple comma-separated list of tables (denoting inner join operations):

FROM t1, t2, ..., tN

If an outer join operators dictates that a table t_j is joined with an outer join, the FROM clause looks like this:

FROM (t1, ..., tbl) LEFT JOIN t_j ON outer_join_predicates, ... tN

Here, all tables used by outer_join_predicates are moved to the left (which is in order because an inner join is commutative).

Predicates

A predicate that refers to an INNER table and OUTER table dictates that the INNER table is joined with an outer join operation.

A predicate that only refers to an INNER table (like t1.col(+)=124) is added to the table's ON expression, provided there is another predicate that dictates that the inner table is joined with outer join operation (otherwise, the predicate remains in the WHERE clause and a warning is issued).

Examples

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

See Also

• Joining Tables with JOIN Clauses Guide

• More Advanced Joins

• Comma vs JOIN

• Joins, Subqueries and SET

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}