inner join

Syntax

select column_name(s) from leftTable inner join rightTable on leftTable.matchingCol=rightTable.rightMatchingCol

or

select column_name(s)
from table1 inner join table2
on table1.column_name=table2.column_name and [filter]

Arguments

filter is condition expression(s) used as filter condition(s) for the join. Use “and” or “or” to join multiple conditions.

Details

Return only the rows that have equivalent values for the matching columns. This function returns the same result as equi join.

Note:

1. You can use and to combine multiple conditions for a table join.

2. It cannot be used with the keyword update.

3. If the left table is not a DFS table, its right table cannot be a DFS table either.

Examples

Example 1. Inner join two tables with no common column names except the matching column(s)

$ t1= table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value)
$ t2 = table(5 3 1 as id, 300 500 800 as qty);
$ select id, value, qty from t1 inner join t2 on t1.id=t2.id

id	value	qty
1	7.8	800
3	5.1	500
3	0.1	500

$ select id, value, qty from t1 inner join t2 on t1.id=t2.id where id=3

id	value	qty
3	5.1	500
3	0.1	500

Example 2. Inner join two tables with common variables that are not the matching column(s)

$ t3 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value, 64 73 52 66 as x);
$ t4 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x) ;
$ select id, value, qty, x from t3 inner join t4 on t3.id=t4.id

id	value	qty	x
1	7.8	800	64
3	5.1	500	52
3	0.1	500	66

If you do not specify which table the columns “value” and “qty” come from, the system will first locate a column in the left table. If it is not in the left table, then the system will locate it in the right table.

$ select id, value, qty, t4.x from t3 inner join t4 on t3.id=t4.id

id	value	qty	x
1	7.8	800	88
3	5.1	500	52
3	0.1	500	66

Example 3. Multiple join columns:

$ select id, value, qty, x from t3 inner join t4 on t3.id=t4.id and t3.x=t4.x

id	value	qty	x
3	0.1	500	66

Example 4. Inner join DFS tables:

$ dbName1="dfs://sql_inner_join"
$ if(existsDatabase(dbName1)){
$   dropDatabase(dbName1)
$ }
$ db1=database(dbName1, RANGE, 1 30 70 101)
$ t1=table("A"+string(1..100) as sym, 1..100 as val)
$ pt1=db1.createPartitionedTable(t1, `pt1, `val).append!(t1)
$ t2=table("A"+string(1..20) as sym, 1..20 as val)
$ pt2=db1.createPartitionedTable(t2, `pt2, `val).append!(t2)

$ select * from pt1 inner join pt2 on pt1.val=pt2.val

sym	val	pt2_sym
A1	1	A1
A2	2	A2
A3	3	A3
A4	4	A4
A5	5	A5
A6	6	A6
A7	7	A7
A8	8	A8
A9	9	A9
A10	10	A10
A11	11	A11
A12	12	A12
A13	13	A13
A14	14	A14
A15	15	A15
A16	16	A16
A17	17	A17
A18	18	A18
A19	19	A19
A20	20	A20

Example 5. Specify filter conditions

$ t1= table(1 2 3 3 6 8 as id, 7.8 4.6 5.1 0.1 0.5 1.2 as value)
$ t2 = table(5 3 1 2 6 8 as id, 300 500 800 400 600 700 as qty);
$ select * from t1 inner join t2 on t1.id=t2.id and t1.id>=3

id	value	qty
3	5.1	500
3	0.1	500
6	0.5	600
8	1.2	700