Example 1. Right join two tables with no common column names except the join
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);
t1;
id |
value |
1 |
7.8 |
2 |
4.6 |
3 |
5.1 |
3 |
0.1 |
t2;
SELECT id, value, qty FROM t1 RIGHT OUTER JOIN t2 ON t1.id=t2.id
//equivalent to SELECT id, value, qty FROM t1 RIGHT JOIN t2 ON t1.id=t2.id
id |
value |
qty |
5 |
|
300 |
3 |
5.1 |
500 |
3 |
0.1 |
500 |
1 |
7.8 |
800 |
If we do not specify which table the columns "value" and "qty" come from, the system
will first locate a column in the right table. If it is not in the right table, then
the system will locate it in the left table.
SELECT id, value, qty FROM t2 RIGHT JOIN t1 ON t2.id=t1.id
id |
value |
qty |
1 |
7.8 |
800 |
2 |
4.6 |
|
3 |
5.1 |
500 |
3 |
0.1 |
800 |
Example 2. Right join two tables with common variables that are not the joining
column(s)
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 as id, 300 500 800 as qty, 44 66 88 as x);
t1;
id |
value |
x |
1 |
7.8 |
4 |
2 |
4.6 |
3 |
3 |
5.1 |
2 |
3 |
0.1 |
1 |
t2;
id |
qty |
x |
5 |
300 |
44 |
3 |
500 |
66 |
1 |
800 |
88 |
SELECT id, value, qty, x FROM t1 RIGHT JOIN t2 ON t1.id=t2.id
id |
value |
qty |
x |
5 |
|
300 |
44 |
3 |
5.1 |
500 |
66 |
3 |
0.1 |
500 |
66 |
1 |
7.8 |
800 |
88 |
SELECT id, value, qty, t1.x FROM t1 RIGHT JOIN t2 ON t1.id=t2.id
id |
value |
qty |
x |
5 |
|
300 |
|
3 |
5.1 |
500 |
2 |
3 |
0.1 |
500 |
1 |
1 |
7.8 |
800 |
4 |
To select variable x from the left table (t1) when the right table (t2) has a
variable with the same name, we need to specify t1.x.
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id=t2.id
id |
qty |
x |
value |
t1_x |
5 |
300 |
44 |
|
|
3 |
500 |
66 |
5.1 |
2 |
3 |
500 |
66 |
0.1 |
1 |
1 |
800 |
88 |
7.8 |
4 |
Here right join selects x from both t1 and t2, and renames x from t1 as t1_x.
Example 3. Multiple join columns
t1=table(1 1 2 2 3 3 as x, 1 2 2 3 3 4 as y, 1..6 as a);
t2=table(0 1 1 2 2 3 as x, 1 2 3 3 4 5 as y, 11..16 as b);
t1;
x |
y |
a |
1 |
1 |
1 |
1 |
2 |
2 |
2 |
2 |
3 |
2 |
3 |
4 |
3 |
3 |
5 |
3 |
4 |
6 |
t2;
x |
y |
b |
0 |
1 |
11 |
1 |
2 |
12 |
1 |
3 |
13 |
2 |
3 |
14 |
2 |
4 |
15 |
3 |
5 |
16 |
SELECT * FROM t1 RIGHT JOIN t2 ON t1.x=t2.x AND t1.y=t2.y
// x and y are join columns
x |
y |
b |
a |
0 |
1 |
11 |
|
1 |
2 |
12 |
2 |
1 |
3 |
13 |
|
2 |
3 |
14 |
4 |
2 |
4 |
15 |
|
3 |
5 |
16 |
|
t2.rename!(`x`y, `x2`y2);
t2
x2 |
y2 |
b |
0 |
1 |
11 |
1 |
2 |
12 |
1 |
3 |
13 |
2 |
3 |
14 |
2 |
4 |
15 |
3 |
5 |
16 |
SELECT * FROM t1 RIGHT JOIN t2 ON t1.x=t2.x2 AND t1.y=t2.y2
// t1.x, t1.y t2.x2, t2.y2 are join columns
x |
y |
b |
a |
0 |
1 |
11 |
|
1 |
2 |
12 |
2 |
1 |
3 |
13 |
|
2 |
3 |
14 |
4 |
2 |
4 |
15 |
|
3 |
5 |
16 |
|
Example 4. Specify filter conditions
t1= table(1 2 3 3 as id1, 7.8 4.6 5.1 0.1 as value)
t2 = table(5 3 1 as id2, 300 500 800 as qty);
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id1=t2.id2 AND t1.value>1 AND t1.value<6 AND t2.qty>300
id1 |
qty |
value |
5 |
300 |
|
3 |
500 |
5.1 |
1 |
800 |
|