LEFT and RIGHT JOINS reverse behavior for some tables


#1

It seems like the behavior of LEFT vs RIGHT JOINS can flip, depending on which tables I’m querying.
Any idea what’s going on? thanks

cr> select "test1"."id" "a", "test2"."id" "b" from "test1" left join "test2" on 1=2;
+---+------+
| a |    b |
+---+------+
| 3 | NULL |
+---+------+
SELECT 1 row in set (0.003 sec)
cr> select "test1"."id" "a", "test2"."id" "b" from "test1" right join "test2" on 1=2;
+------+---+
|    a | b |
+------+---+
| NULL | 5 |
+------+---+
SELECT 1 row in set (0.001 sec)
cr> select "orgs"."org-name", "sessions"."user-id" from orgs left join sessions on 1=2;
+----------+--------------------+
| org-name | user-id            |
+----------+--------------------+
|     NULL | UUID-users-a@f.com |
+----------+--------------------+
SELECT 1 row in set (0.001 sec)
cr> select "orgs"."org-name", "sessions"."user-id" from orgs right join sessions on 1=2;
+-------------+---------+
| org-name    | user-id |
+-------------+---------+
| Buyer Org 1 |    NULL |
| Vendor Org2 |    NULL |
+-------------+---------+
SELECT 2 rows in set (0.003 sec)

#2

Hi,

LEFT JOIN and RIGHT JOIN are showing the expected behavior for the table design. After looking at the above queries, I guess they might be missing a common field or relation between the two tables. Our documentation for JOINS is available here - https://crate.io/docs/crate/reference/en/latest/general/dql/joins.html?highlight=joins#right-outer-joins
https://crate.io/docs/crate/reference/en/latest/general/dql/joins.html?highlight=joins#left-outer-joins
Kindly refer to the example of Employee, Department tables in above and let us know if it helps.

Thanks.