CRM_Utils_SQL_Select - Allow construction of UNION/INTERSECT with sub-queries
MySQL 5.7 supports "UNION" (which combines the result-sets from two queries). MySQL 8.0
and MariaDB 10.3 expand this more set-operations (eg "INTERSECT", "EXCEPT").
The patch allows CRM_Utils_SQL_Select to construct a range of queries with these
operators.
Before
------
Not supported
After
-----
A few examples:
```php
CRM_Utils_SQL_Select::fromSet()->union([$subQuery1, $subQuery2])
CRM_Utils_SQL_Select::fromSet()
->union($subQuery1)
->union($subQuery2);
CRM_Utils_SQL_Select::fromSet()
->setOp('UNION ALL', [$subQuery1, $subQuery2])
->setOp('EXCEPT', [$subQuery3]);
```
Comments
--------
In these examples, we build one top-level `SELECT` query, and then
use a sub-query to get the UNION/INTERSECT/EXCEPT data. This is not
strictly necessary. (SQL allows set-ops without a top-level SELECT.)
However, this pattern is powerful - because you can freely mix-in
other subclauses (`WHERE`/`GROUP BY`/`HAVING`/etc).
I'm not 100% certain that these signatures are best. Looking at the MySQL
BNF, this is still a bit reductive wrt precedence of the UNION / EXCEPT / INTERSECT
operations. However, `CRM_Utils_SQL_Select` is already reductive about precedence
of WHEREs.
Part of me wanted to model the UNION/setops as a separate object-type
(independent of SELECT) -- and then improve subquery support for SELECT, eg
```
$union = new CRM_Utils_SQL_SetOp('UNION', [$subQuery1, $subQuery2]);
$select = CRM_Utils_SQL_Select::from($union)->where('...');
```
However, in the CiviMail PR that uses UNION, there's an existing `Select`
object where they iteratively mix-in more union clauses. It seems that the
`Select::fromSet()->union()` might be a bit more amenable.