JOIN

SELECT column_name(s)
FROM table1
JOIN table2
[ON table1.matchingCol1=table2.MatchingCol2]

table1 and table2 are the tables to be joined.

matchingCol1 and matchingCol2 are the join columns.

• When an ON condition is not specified, perform CROSS JOIN and return the Cartesian product of the two tables.

• When an ON condition is specified, perform INNER JOIN and return the joined records that meet the join condition.

Note:

• Non-equi join is currently not supported. For example, t1 join t2 on t1.id <op> t2.id, where <op> cannot be >, <,>=, <=, or <>.

• Multiple join columns must be connected with AND.

• It cannot be used with keyword UPDATE.

Before version 1.30.22, select * from table1 join table2 is equivalent to join(a,b), i.e., merging the two tables.

t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value, 4 3 2 1 as x)
t2 = table(5 3 1 2 as idv,  300 500 800 200 as qty, 4 6 4 5 as xv);
SELECT * FROM t1 JOIN t2 WHERE x>3
//equivalent to SELECT * FROM t1 CROSS JOIN t2 WHERE x>3 or SELECT * FROM cj(t1,t2) WHERE x>3;

SELECT * FROM t1 JOIN t2 ON t1.id=t2.idv ;
//equivalent to SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.idv