right join/right outer join

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.

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.

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.

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;

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

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

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;

t2;

SELECT id, value, qty, x FROM t1 RIGHT JOIN t2 ON t1.id=t2.id

SELECT id, value, qty, t1.x FROM t1 RIGHT JOIN t2 ON t1.id=t2.id

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

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;

t2;

SELECT * FROM t1 RIGHT JOIN t2 ON t1.x=t2.x AND t1.y=t2.y
// x and y are join columns

t2.rename!(`x`y, `x2`y2);
t2

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

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