right join/right outer join

Syntax

Right join

SELECT column_name(s)
FROM leftTable RIGHT [OUTER] JOIN rightTable
ON leftTable.matchingCol=rightTable.rightMatchingCol and [filter]

Note:

  1. Joining more than two tables simultaneously is not supported.

  2. If there are multiple join columns, they must be linked with AND.

  3. RIGHT JOIN cannot be used with the UPDATE keyword.

Arguments

filter can be condition expressions used as filter conditions for the columns in the left and right tables. Use AND to join multiple conditions; OR is currently not supported.

Details

RIGHT JOIN returns all records from the right table and the matched records from the left table. The result is NULL from the left table if there is no match. If there are more than one matched record in the left table, all the matched records in the left table are returned. Right join may return more rows than the right table.

Examples

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;
id qty
5 300
3 500
1 800
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