inner join

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]

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

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.

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

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

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

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

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

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

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