Previous Next JavaScript must be enabled to correctly display this content

## Inner Join

An inner join is an operation that produces new rows by combining rows from two or more tables, based on the join predicates applied to related columns or fields between them. The result-set contains only those combined rows that satisfy the join predicates.

Syntax

The tables to be joined are specified in the FROM clause of the SELECT statement, separated by commas.

    innerjoin_from_clause ::= FROM innerjoin_tables (COMMA innerjoin_tables)* (COMMA unnest_syntax)*

    innerjoin_table_clause ::= single_from_table|aliased_table_name|left_outer_join_tables|nested_tables

For description of various elements of the syntax, see FROM clause. Note that the syntax implies that we can not only join individual tables, but also the result of a NESTED TABLES clause (or left outer join list) with an individual table, or with the result of another NESTED TABLES clause (or left outer join list).

Semantics

The FROM clause specifies the participating tables. The name of any participating table in the join may be followed by a table alias.

Let us consider that we perform an inner join of two tables A and B. Table A has N rows and n columns, and table B has M rows and m columns. Conceptually, every row in table A is joined with every row in table B. The resultant table AB would have N * M rows and n + m columns.

Let us understand how inner join works using a simple example. In the below example table A has 3 rows, 3 columns with CA1 as the primary key/shard key; and its child table B has 2 rows, 3 columns with CB1 as the primary key.

Table 6-10 Table A

CA1 | CA2 | CA3
--- | --- | ---
RA11 | RA12 | RA13
RA21 | RA22 | RA23
RA31 | RA32 | RA33

Table 6-11 Table B

(CA1) | CB1 | CB2 | CB3
--- | --- | --- | ---
RA11 | RB11 | RB12 | RB13
RA21 | RB21 | RB22 | RB23

Column (CA1) is the inherited shard key from table A.

Table 6-12 Combined Table AB (every row in table A is combined with every row in table B)

CA1 | CA2 | CA3 | (CA1) | CB1 | CB2 | CB3
--- | --- | --- | --- | --- | --- | ---
RA11 | RA12 | RA13 | RA11 | RB11 | RB12 | RB13
RA11 | RA12 | RA13 | RA21 | RB21 | RB22 | RB23
RA21 | RA22 | RA23 | RA11 | RB11 | RB12 | RB13
RA21 | RA22 | RA23 | RA21 | RB21 | RB22 | RB23
RA31 | RA32 | RA33 | RA11 | RB11 | RB12 | RB13
RA31 | RA32 | RA33 | RA21 | RB21 | RB22 | RB23

The WHERE clause is then applied to this combined table. The WHERE clause contains the join predicates that specifies the columns or fields on which the records from the tables are joined.
In the case of inner join, the WHERE clause must include the equality predicate on all shard keys of the participating tables. Therefore, after applying this predicate, the final table after inner join would be:

Table 6-13 Result after inner join (based on shard key equality predicate)

CA1 | CA2 | CA3 | (CA1) | CB1 | CB2 | CB3
--- | --- | --- | --- | --- | --- | ---
RA11 | RA12 | RA13 | RA11 | RB11 | RB12 | RB13
RA21 | RA22 | RA23 | RA21 | RB21 | RB22 | RB23

If there are other join predicates in the WHERE clause, those are then applied.

When joining more than 2 tables, say A, B, C, and D, the FROM clause is applied first to tables A and B producing table AB. In a similar manner, the FROM clause is applied to tables AB and C, producing table ABC. Lastly, the FROM clause is applied to ABC and D, producing table ABCD. The join predicates in the WHERE clause are then applied to this combined table ABCD.

While performing an inner join, the following are applicable:

- Only joins among tables in the same table hierarchy are allowed.
- Supports joining of tables that are in an ancestor-descendant relationship as well as tables that are not in an ancestor-descendant relationship.
- The join predicates must include equality predicates between all the shard key columns of the joined tables. That is, for any pair of joined tables, a row from one table will match with a row from the other table only if they both have the same values on their shard key columns. To understand more about shard keys, see CREATE TABLE . You can use the DESCRIBE TABLE statement to identify the shard keys.
- The rest of the predicates in the WHERE clause are applied to these matching rows.

Ordering of columns in the result-set of inner join:

If you use a '*' with the SELECT clause, wherein all the fields in the tables are returned, the order of fields in the result-set depends on the order in which you specify the tables in the FROM clause. If you provide a list of fields in the SELECT clause, then the order of the fields is as specified in the SELECT clause.