Joins

Outer Joins

{id:1,name:"foo",src:"za"}
{id:3,name:"qux",src:"za"}

{id:1,name:"foo",src:"zb"}
{id:2,name:"bar",src:"zb"}

Left Join (Left Only + Inner Joins — where clause required to eliminate inner joins)

select * includes columns from both sides. The right table’s columns get a _1 suffix to avoid name collisions, and unmatched values are error("missing").

super -s -c "select * from za.sup as za
             left join zb.sup as zb
             on za.id=zb.id
             where is_error(zb.name)"

{id:3,name:"qux",src:"za",id_1:error("missing"),name_1:error("missing"),src_1:error("missing")}

Right Join (Right Only + Inner Joins — where clause required to eliminate inner joins)

super -s -c "select * from za.sup as za
             right join zb.sup as zb
             on za.id=zb.id
             where is_error(za.name)"

{id:error("missing"),name:error("missing"),src:error("missing"),id_1:2,name_1:"bar",src_1:"zb"}

Anti Join (Left Join exclusively — no where clause required)

super -s -c "select * from za.sup as za
             anti join zb.sup as zb
             on za.id=zb.id"

{id:3,name:"qux",src:"za",id_1:error("missing"),name_1:error("missing"),src_1:error("missing")}

Full Outer (Left Only + Right Only + Inner Joins) - BUG: Still behaves like Left Join — only returns left-side rows

super -s -c "select * from za.sup as za
             full outer join zb.sup as zb
             on za.id=zb.id
             where is_error(za.name) or is_error(zb.name)"

{id:3,name:"qux",src:"za",id_1:error("missing"),name_1:error("missing"),src_1:error("missing")}

as of versions

super --version

Version: v0.2.0