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 1.30.22/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 |