Purpose
The JOIN clause is used to combine rows from two or more tables based on matching columns between them.
Privilege requirements
When performing a table join (JOIN), the current user must have at least the SELECT privilege on all tables involved in the JOIN. For more information about OceanBase AI Database privileges, see Permission classification.
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
Parameter |
Description |
|---|---|
| table_reference | Represents a table reference, which can be a single table, a joined table, or a subquery. |
| table_factor | The data source table for the query, which can also be a subquery of a table or a table reference within parentheses. For more information, see table_factor. |
| joined_table | Represents a joined table. For more information, see joined_table. |
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: The name of the table or view from which to retrieve data. OceanBase AI Database supports querying tables in other databases using DBLink (table_name@dblink_name). For more information about using DBLink, see Access data in a remote database through DBLink.PARTITION (partition_name_list): Optional. Specifies the query for a specific partition.partition_name_list: The list of partition names.partition_nameindicates a partition name.
Example:
Read data from partitions
p0andp1of tabletbl1.SELECT * FROM tbl1 PARTITION(p0, p1);The return result is as follows:
+------+------+------+ | col1 | col2 | col3 | +------+------+------+ | 1 | A1 | 1 | +------+------+------+ 1 row in setsample_clause [SEED(integer)]: Specifies the sampling rule for the subquery, which scans a subset of records. Details are as follows:BLOCK: Optional. Specifies to scan random data blocks. The default is to scan random rows.ALL | BASE | INCR: Optional. Specifies the scan method. Valid values:ALL: scans all data. The default value isALL.BASE: Indicates that baseline data is being scanned.INCR: Indicates that incremental data is being scanned.
sample_percent: the sampling ratio, in %. It can be of theINTEGERorDECIMALtype.SEED(integer): Optional. 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: Optional. Specifies a table alias.index_hint_list: Optional. Specifies the list of index hints to use for the query. Anindex_hintspecifies the index to use for the query. The details are as follows:USE | FORCE | IGNORE:USE: Indicates that an operation uses an index.FORCE: forces the use of indexes for certain operations.IGNORE: specifies that an operation does not use an index.
KEY | INDEX: Specifies the index used for searching and sorting in the query.FOR {JOIN | ORDER BY | GROUP BY}: Optional. Specifies the operations performed on the index. Details are as follows:FOR JOIN: Specifies the index for the join.FOR ORDER BY: Specifies the index for sorting.FOR GROUP BY: Specifies the index for grouping.
index_name_list: The list of index names to use. You can specify one or more index names, which must be separated by commas (,).index_namespecifies the name of the index being referenced.
table_subquery [AS] table_alias_name: specifies the subquery. For more information about subqueries, see Subqueries.Example:
Select the
col1column from tabletbl1, then name it as a subqueryt1, and finally select all columns fromt1.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]: Indicates an inner join. It returns the intersection of matching rows from the two tables. In this type of join, no results are returned if no matching rows exist. Specifically:table_reference: Indicates a table reference, which can be a single table, a joined table, or a subquery.NATURAL: Optional. Indicates to perform a natural join.NATURAL JOINautomatically uses the same columns for joining.INNER | CROSS: Optional. Valid values:INNER JOIN: Indicates an inner join. When no join condition is specified,INNER JOINis equivalent to a comma operator (,) in English, as both generate a Cartesian product of the specified tables. For more information about inner joins, see INNER JOIN.Notice
An English comma (
,) is an operator with a lower precedence thanINNER JOIN,CROSS JOIN, andLEFT JOIN. Therefore, mixing commas with other join keywords may lead to incorrect conditions in theONclause.CROSS JOIN: In OceanBase AI Database,CROSS JOINis equivalent toJOINandINNER JOIN.
table_factor: the reference to the table to be joined. For more information, see table_factor.join_condition: Optional. Specifies the join condition. Valid values:ON expression: The join condition for the duplicate columns, applicable when joining on columns with different names. You can specify any join condition, not limited to equality; for example, you can use conditions such as greater than or less than.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 setUSING (join_column_list): The join condition does not return duplicate columns. It is limited to using columns with the same name in the joined tables as the join condition.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: Indicates an outer join. It returns the result of all rows from both tables, whether there is a match or not. In this type of join, if no matching row exists,NULLis used to fill in the gap.outer_join_type: Specifies the outer join type. Valid values:{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,NULLis automatically filled in for that row 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 corresponding match in the left table,NULLis automatically filled in the left table. For more information about right outer joins, see RIGHT JOIN.FULL [OUTER] JOIN: Indicates a full outer join. When no matching row is found in the left or right table,NULLis automatically filled in. For more information about full outer joins, see FULL JOIN.
join_condition: the join condition.
Example:
Select all columns from table
tbl1and perform a left join with the data in tabletbl2. The join condition istbl1.col1 = tbl2.col1, and the conditiontbl1.col1 > 2is used to filter out data that meets this criterion.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