JOIN clause

Purpose

The JOIN clause joins rows from two or more tables based on related columns in the tables.

Privilege requirements

To execute a table join (JOIN), the current user must have the SELECT privilege on at least one of the tables involved in the JOIN. For more information about privileges in 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. The syntax is as follows:

  • table_name: specifies the name of the table or view from which to retrieve data. You can use a DBLink to query a table in another database (table_name@dblink_name). For more information about DBLinks, see Access data in a remote database through a DBLink.

    Note

    This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support DBLinks.

  • PARTITION (partition_name_list): an optional clause that specifies the partitions to be queried.

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

    Here are some examples:

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

    SELECT * FROM tbl1 PARTITION(p0, p1);
    

    The return result is as follows:

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

  • sample_clause [SEED(integer)]: specifies the sampling rules, which indicate that only a portion of records are scanned. The syntax is as follows:

    • BLOCK: an optional clause that indicates that random data blocks are scanned. The default is to scan random rows.

    • ALL | BASE | INCR: an optional clause that specifies the scan mode. The syntax is as follows:

      • ALL: indicates that all data is scanned. The default is ALL.
      • BASE: indicates that baseline data is scanned.
      • INCR: indicates that incremental data is scanned.

    • sample_percent: specifies the sampling ratio in percentage. The data type can be INTEGER or DECIMAL.

    • SEED(integer): an optional clause that specifies the seed value for random sampling. The value range is [0,4294967295]. The same sampling seed value always returns the same result.

  • [AS] table_alias_name: an optional clause that specifies a table alias.

  • index_hint_list: an optional clause that specifies a list of index hints for the query. index_hint specifies an index hint, indicating the index to be used for the query. The syntax is as follows:

    • USE | FORCE | IGNORE:

      • USE: indicates that the specified operation uses an index.
      • FORCE: indicates that the specified operation must use an index.
      • IGNORE: indicates that the specified operation does not use an index.

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

    • FOR {JOIN | ORDER BY | GROUP BY}: an optional clause that specifies the operation to be performed on the index. The syntax is as follows:

      • FOR JOIN: specifies that the index is used for joining.
      • FOR ORDER BY: specifies that the index is used for sorting.
      • FOR GROUP BY: specifies that the index is used for grouping.

    • index_name_list: specifies a 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 Subquery.

  Here are some examples:

  Select the col1 column from the tbl1 table, name the subquery as t1, and select all columns from t1.

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

  The return 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 rows in the two tables. If no matching rows are found, no result is returned. The syntax is described as follows:

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

  • NATURAL: optional. Specifies a natural join. In a natural join, the system automatically uses the same column for joining.

  • INNER | CROSS: optional. Specifies the join type. The options are described as follows:

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

      Notice

      The English comma (`,`) has a lower precedence than INNER JOIN, CROSS JOIN, and LEFT JOIN. Therefore, if you use the comma together with other join keywords, the condition in the ON clause may be incorrect.

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

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

  • join_condition: optional. Specifies the join condition. The syntax is described as follows:

    • ON expression: specifies a join condition that returns duplicate columns. This join condition can be used when different column names are specified as join conditions. You can specify any join condition, not just an equal condition. For example, you can use a greater-than or less-than condition for joining.

      Here is an example:

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

      The return 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 a join condition that does not return duplicate columns. This join condition is applicable only when the same column names are specified as join conditions.

      Here is an example:

      SELECT *
        FROM tbl1 JOIN tbl2 
          USING (col1);
      

      The return 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, whether or not there are matching rows. If no matching rows are found, NULL is automatically filled in.

  • outer_join_type: specifies the type of outer join. The options are described as follows:

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

      • LEFT [OUTER] JOIN: specifies a left outer join. If a row in the left table is not found in the right table, NULL is automatically filled in for the row in the right table. For more information about left outer joins, see LEFT JOIN.
      • RIGHT [OUTER] JOIN: specifies a right outer join. If a row in the right table is not found in the left table, NULL is automatically filled in for the row in the left table. For more information about right outer joins, see RIGHT JOIN.
      • FULL [OUTER] JOIN: specifies a full outer join. If a row is not found in either the left table or the right table, NULL is automatically filled in for the row. For more information about full outer joins, see FULL JOIN.

  • join_condition: specifies the join condition.

  Here is an example:

  Select all columns from the tbl1 table and join the data in the tbl2 table with the tbl1 table on the condition that tbl1.col1 = tbl2.col1. The data that satisfies the condition tbl1.col1 > 2 is filtered out.

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

  The return 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
• Subqueries
• Joins