MechanicalRabbit
Support for SQL set operators UNION/INTERSECT/EXCEPT
How should set operators be represented in FunSQL?
In SQL, the mandatory SELECT gives each argument of the set operator an explicit list of columns. But in FunSQL, the column list is often implied. For this reason, some operators have custom logic for constructing the column list of their output. For example, Append (UNION ALL) has output column list generated as an intersection of column names of it arguments. While this is adequate for UNION ALL, it does not seem to be appropriate for UNION since for the latter the column list materially affects the output of the operator. In this respect UNION is closer to GROUP BY and, in fact, it can be expressed as UNION ALL followed by GROUP BY.
This suggests an interface that requires an explicit column list as one of the parameters of the operator:
SetUnion(; args, by)SetIntersection(other; by)SetDifference(other; by)
(However, these names do not match other FunSQL "verb" operator names)
Why not stick closer to SQL's names? they are verbs and except might even be preferred over difference because it is a verb. In this these cases, we're not fundamentally redefining the operation, e.g. select is a operation on fields, filtering and reordering them.
