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
RA₁₁	RA₁₂	RA₁₃
RA₂₁	RA₂₂	RA₂₃
RA₃₁	RA₃₂	RA₃₃

Table 6-11 Table B

(CA1)	CB1	CB2	CB3
RA₁₁	RB₁₁	RB₁₂	RB₁₃
RA₂₁	RB₂₁	RB₂₂	RB₂₃

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
RA₁₁	RA₁₂	RA₁₃	RA₁₁	RB₁₁	RB₁₂	RB₁₃
RA₁₁	RA₁₂	RA₁₃	RA₂₁	RB₂₁	RB₂₂	RB₂₃
RA₂₁	RA₂₂	RA₂₃	RA₁₁	RB₁₁	RB₁₂	RB₁₃
RA₂₁	RA₂₂	RA₂₃	RA₂₁	RB₂₁	RB₂₂	RB₂₃
RA₃₁	RA₃₂	RA₃₃	RA₁₁	RB₁₁	RB₁₂	RB₁₃
RA₃₁	RA₃₂	RA₃₃	RA₂₁	RB₂₁	RB₂₂	RB₂₃

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
RA₁₁	RA₁₂	RA₁₃	RA₁₁	RB₁₁	RB₁₂	RB₁₃
RA₂₁	RA₂₂	RA₂₃	RA₂₁	RB₂₁	RB₂₂	RB₂₃

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.