Join and Cross

In this example, we will use a self join. We create two relations and rename them and then apply the operators join, cross, union and except

[[nom::foo]] [[type::selective]] [[amount::500000]]
[[nom::foo]] [[type::automatic]] [[amount::400000]]
[[nom::bar]] [[type::selective]] [[amount::300000]]
[[nom::ccc]] [[type::selective]] [[amount::250000]]
[[nom::ddd]] [[type::automatic]] [[amount::150000]]

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
= 3 rows 4 msec

nom	selective
bar	300000
ccc	250000
foo	500000

+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
|  RENAME amount automatic
= 2 rows 4 msec

nom	automatic
ddd	150000
foo	400000

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  JOIN nom
= 1 row 9 msec

nom	selective	automatic
foo	500000	400000

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  LEFTJOIN nom
= 3 rows 9 msec

nom	selective	automatic
bar	300000
ccc	250000
foo	500000	400000

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  OUTERJOIN nom
= 4 rows 9 msec

nom	selective	automatic
bar	300000
ccc	250000
foo	500000	400000
ddd		150000

Note that in relations, tuples are sets and have no predefined orders of their parameters.

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  CROSS
= 6 rows 9 msec

nom	automatic	selective
ddd	150000	300000
ddd	150000	250000
ddd	150000	500000
foo	400000	300000
foo	400000	250000
foo	400000	500000

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  UNION
= 5 rows 9 msec

nom	selective	automatic
bar	300000
ccc	250000
foo	500000
ddd		150000
foo		400000

The same note on relations apply. The returned relation from UNION has tuples with the same parameters, but not in a particular order.

+  SELECT nom, amount FROM Join and Cross WHERE type == selective
|  RENAME amount selective
|+  SELECT nom, amount FROM Join and Cross WHERE type == automatic
||  RENAME amount automatic
|/  EXCEPT nom
= 2 rows 10 msec

nom	selective
bar	300000
ccc	250000