Skip to content

APIv4 SET Operations

Background

SQL Set Operations are ways of combining the results of multiple queries.

  • UNION ALL: Combines the results of two or more SELECT statements into a single result set, eliminating duplicate rows.
  • UNION DISTINCT: A UNION which eliminates duplicate rows.
  • INTERSECT: Returns the common elements between two or more SELECT statements, resulting in a result set that contains only the shared rows.
  • EXCEPT: Retrieves the rows from the first SELECT statement that are not present in the result set of the second SELECT statement, effectively finding the difference between the two result sets.

As of version 5.64, APIv4 adds support for UNION ALL and UNION DISTINCT. It does not yet support INTERSECT or EXCEPT because the minimum version of MySql would need to be bumped to 8.0. Once that's done in a future version, those operations can be supported as well.

The EntitySet API

By calling the EntitySet API and adding 2 or more sets to combine, you effectively create a new entity/table which can be operated on as a whole (SELECT, WHERE, HAVING, ORDER BY, LIMIT).

For example, you could UNION together a set of Contributions and Contribution Soft Credits, then apply a GROUP BY clause to the combined results.

Warning

Per the rules of SQL Set Operations, each set must have the same number of fields selected, with matching data types. They will be combined in that order (not by name) and the field names of the first set will be used for the entire EntitySet.

Try it yourself in the API Explorer:

APIv4 Explorer EntitySet