JOIN clause

Purpose

The JOIN clause is used to join rows from two or more tables based on related columns between these tables.

Privilege requirements

When you execute a table join (JOIN), the current user must have the SELECT privilege on the tables involved in the JOIN. For more information about the privileges of OceanBase Database, see Privilege types in MySQL mode.

Syntax

table_references:
    table_reference [, table_reference ...]

table_reference:
    table_factor
    | joined_table

table_factor:
    table_name [PARTITION (partition_name_list)] [sample_clause [SEED(integer)]] [[AS] table_alias_name][index_hint_list]
    | table_subquery [AS] table_alias_name
    | (table_references)

partition_name_list:
    partition_name [, partition_name ...]

sample_clause:
    SAMPLE [BLOCK] [ALL | BASE | INCR] (sample_percent)

index_hint_list:
    index_hint [, index_hint ...]

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

index_list:
    index_name [, index_name ...]

table_subquery:
    (select_stmt [sample_clause [SEED(integer)]])

joined_table:
    table_reference [NATURAL] [INNER | CROSS] JOIN table_factor [join_condition]
    | table_reference outer_join_type JOIN table_factor join_condition

join_condition:
    ON expression
    | USING (join_column_list)

join_column_list:
    column_name [, column_name ...]

outer_join_type:
    [NATURAL] {LEFT | RIGHT | FULL} [OUTER]

Parameters

table_factor

• table_name [PARTITION (partition_name_list)] [sample_clause [SEED(integer)]] [[AS] table_alias_name] [index_hint_list]: specifies the data source table for the query. Details are as follows:

  • table_name: specifies the name of the table or view from which to retrieve data. OceanBase Database supports querying tables in other databases using DBLink (syntax: table_name@dblink_name). For more information about using DBLink, see Access data in a remote database by using DBLink.

    Note

    This feature is available only in OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support DBLink.

  • PARTITION (partition_name_list): optional. Specifies the partitions to query.

    • partition_name_list: the list of partition names. partition_name specifies the name of a partition.

    Here is an example:

    Read data from partitions p0 and p1 of the tbl1 table.

    SELECT * FROM tbl1 PARTITION(p0, p1);
    

    The result is as follows:

    +------+------+------+
    | col1 | col2 | col3 |
    +------+------+------+
    |    1 | A1   |    1 |
    +------+------+------+
    1 row in set
    

  • sample_clause [SEED(integer)]: specifies the sampling rules for the table, that is, to scan a portion of the records. Details are as follows:

    • BLOCK: optional. Specifies to scan random data blocks. By default, random rows are scanned.

    • ALL | BASE | INCR: optional. Specifies the scan method. Details are as follows:

      • ALL: specifies to scan all data. The default value is ALL.
      • BASE: specifies to scan baseline data.
      • INCR: specifies to scan incremental data.

    • sample_percent: specifies the sampling ratio, in percentage. You can use the INTEGER or DECIMAL type.

    • SEED(integer): optional. Specifies the random sampling seed. The value ranges from [0,4294967295]. The same sampling seed always returns the same result.

  • [AS] table_alias_name: optional. Specifies the table alias.

  • index_hint_list: optional. Specifies the list of index hints for the query. index_hint specifies the index to be used in the query. Details are as follows:

    • USE | FORCE | IGNORE:

      • USE: specifies to use an index.
      • FORCE: specifies to force the use of an index.
      • IGNORE: specifies not to use an index.

    • KEY | INDEX: specifies the index to be used for searching and sorting in the query.

    • FOR {JOIN | ORDER BY | GROUP BY}: optional. Specifies the operation to be performed by the index. Details are as follows:

      • FOR JOIN: specifies to use the index for joining.
      • FOR ORDER BY: specifies to use the index for sorting.
      • FOR GROUP BY: specifies to use the index for grouping.

    • index_name_list: specifies the list of index names to be used. You can specify one or more index names. Separate multiple index names with commas (,). index_name specifies the name of an index.

• table_subquery [AS] table_alias_name: specifies a subquery. For more information about subqueries, see Subqueries.

  Here is an example:

  Select the col1 column from the tbl1 table, use the result as a subquery named t1, and select all columns from t1.

  SELECT t1.* FROM (SELECT col1 FROM tbl1) t1;
  

  The result is as follows:

  +------+
  | col1 |
  +------+
  |    1 |
  |    2 |
  |    3 |
  +------+
  3 rows in set
  

joined_table

• table_reference [NATURAL] [INNER | CROSS] JOIN table_factor [join_condition]: specifies an inner join. It returns the intersection of matching rows from the two tables. In this type of join, no results are returned if there are no matching rows. Specifically:

  • table_reference: specifies a table reference, which can be a single table, a joined table, or a subquery.

  • NATURAL: an optional keyword that specifies a natural join. NATURAL JOIN automatically joins on columns with the same name.

  • INNER | CROSS: an optional keyword. Specifically:

    • INNER JOIN: specifies an inner join. When no join condition is specified, INNER JOIN is equivalent to using a comma (,), both of which generate a Cartesian product of the specified tables. For more information about inner joins, see INNER JOIN.

      Notice

      When used as an operator, a comma (`,`) has a lower precedence than INNER JOIN, CROSS JOIN, and LEFT JOIN. Therefore, if you mix commas with other join keywords, it may result in incorrect conditions in the ON clause.

    • CROSS JOIN: in OceanBase Database's MySQL mode, CROSS JOIN is equivalent to JOIN and INNER JOIN.

  • table_factor: specifies the table reference to join. For more information, see table_factor.

  • join_condition: an optional clause that specifies the join condition. Specifically:

    • ON expression: specifies the join condition for duplicate columns, applicable when columns with different names are used as the join condition. You can specify any join condition, not limited to equality-based conditions, such as greater than or less than conditions.

      Here is an example:

      SELECT *
        FROM tbl1 JOIN tbl2
          ON tbl1.col1 = tbl2.col1;
      

      The result is as follows:

      +------+------+------+------+------+------+
      | col1 | col2 | col3 | col1 | col2 | col3 |
      +------+------+------+------+------+------+
      |    1 | A1   |    1 |    1 | A1   |    1 |
      |    2 | A2   |    2 |    2 | A2   |   22 |
      |    3 | A3   |    3 |    3 | A3   |   33 |
      +------+------+------+------+------+------+
      3 rows in set
      

    • USING (join_column_list): specifies the join condition without duplicate columns, applicable only when the joined tables use columns with the same name as the join condition.

      Here is an example:

      SELECT *
        FROM tbl1 JOIN tbl2
          USING (col1);
      

      The result is as follows:

      +------+------+------+------+------+
      | col1 | col2 | col3 | col2 | col3 |
      +------+------+------+------+------+
      |    1 | A1   |    1 | A1   |    1 |
      |    2 | A2   |    2 | A2   |   22 |
      |    3 | A3   |    3 | A3   |   33 |
      +------+------+------+------+------+
      3 rows in set
      

• table_reference outer_join_type JOIN table_factor join_condition: specifies an outer join. It returns all rows from the two tables, regardless of whether there are matches. In this type of join, if there are no matching rows, NULL values are filled in.

  • outer_join_type: specifies the type of outer join. Specifically:

    • {LEFT | RIGHT | FULL} [OUTER] JOIN:

      • LEFT [OUTER] JOIN: specifies a left outer join. When a row in the left table does not have a match in the right table, NULL values are automatically filled in the right table. For more information about left outer joins, see LEFT JOIN.
      • RIGHT [OUTER] JOIN: specifies a right outer join. When a row in the right table does not have a match in the left table, NULL values are automatically filled in the left table. For more information about right outer joins, see RIGHT JOIN.
      • FULL [OUTER] JOIN: specifies a full outer join. When a row in the left or right table does not have a match, NULL values are automatically filled in. For more information about full outer joins, see FULL JOIN.

  • join_condition: specifies the join condition.

  Here is an example:

  Select all columns from table tbl1 and perform a left join with table tbl2 on the condition tbl1.col1 = tbl2.col1, and filter the data using the condition tbl1.col1 > 2.

  SELECT *
    FROM tbl1 LEFT JOIN tbl2
      ON tbl1.col1 = tbl2.col1
        AND tbl1.col1 > 2;
  

  The result is as follows:

  +------+------+------+------+------+------+
  | col1 | col2 | col3 | col1 | col2 | col3 |
  +------+------+------+------+------+------+
  |    1 | A1   |    1 | NULL | NULL | NULL |
  |    2 | A2   |    2 | NULL | NULL | NULL |
  |    3 | A3   |    3 |    3 | A3   |   33 |
  +------+------+------+------+------+------+
  3 rows in set
  

References

• SELECT
• Subquery
• Join