JOIN

Syntax

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

Arguments

table1 and table2 are the tables to be joined.

matchingCol1 and matchingCol2 are the join columns.

Details

  • 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 2.00.10, select * from table1 join table2 is equivalent to join(a,b), i.e., merging the two tables.

Examples

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;
id value x idv qty xv
1 7.8 4 5 300 4
1 7.8 4 3 500 6
1 7.8 4 1 800 4
1 7.8 4 2 200 5
SELECT * FROM t1 JOIN t2 ON t1.id=t2.idv ;
//equivalent to SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.idv
id value x qty xv
1 7.8 4 800 4
2 4.6 3 200 5
3 5.1 2 500 6
3 0.1 1 500 6