Proposal: support explicit semi-join and anti-join syntax

[leonard-firebolt]

Summary

We want to support explicit semi-joins and anti-joins in our SQL syntax.

Examples

Left-Semi-Joins

The syntax

SELECT * FROM lhs SEMI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;
SELECT * FROM lhs LEFT SEMI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;

should behave equivalent to

SELECT * FROM lhs WHERE (key_a, key_b) IN (SELECT key_a, key_b FROM rhs);

Right-Semi-Joins

The syntax

SELECT * FROM lhs RIGHT SEMI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;

should behave equivalent to

SELECT * FROM rhs WHERE (key_a, key_b) IN (SELECT key_a, key_b FROM lhs);

Left-Anti-Joins

The syntax

SELECT * FROM lhs ANTI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;
SELECT * FROM lhs LEFT ANTI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;

should behave equivalent to

SELECT * FROM lhs WHERE (key_a, key_b) NOT IN (SELECT key_a, key_b FROM rhs);

Right-Anti-Joins

The syntax

SELECT * FROM lhs RIGHT ANTI JOIN rhs ON lhs.key_a = rhs.key_a AND lhs.key_b = rhs.key_b;

should behave equivalent to

SELECT * FROM rhs WHERE (key_a, key_b) NOT IN (SELECT key_a, key_b FROM lhs);