Table of contents
  1. Supported types
    1. Union
    2. Union All
    3. Except
    4. Intersect
  2. Note on arrays
import { table, union, unionAll, except, intersect } from "../../src";
import * as RNEA from "fp-ts/lib/ReadonlyNonEmptyArray";
import { pipe } from "fp-ts/lib/function";

We will use these tables

CREATE TABLE users(id int, age int, name string);
CREATE TABLE admins(id int, age int, name string);

Which are defined in typescript as

const users = table(["id", "age", "name"], "users");

const admins = table(["id", "age", "name"], "adm", "admins");

Supported types

Union

union([admins.selectStar(), users.selectStar()]).stringify();
SELECT
  *
FROM
  `admins` AS `adm`
UNION
SELECT
  *
FROM
  `users`

Union All

unionAll([admins.selectStar(), users.selectStar()]).stringify();
SELECT
  *
FROM
  `admins` AS `adm`
UNION ALL
SELECT
  *
FROM
  `users`

Except

except([admins.selectStar(), users.selectStar()]).stringify();
SELECT
  *
FROM
  `admins` AS `adm`
EXCEPT
SELECT
  *
FROM
  `users`

Intersect

intersect([admins.selectStar(), users.selectStar()]).stringify();
SELECT
  *
FROM
  `admins` AS `adm`
INTERSECT
SELECT
  *
FROM
  `users`

Note on arrays

Arrays passed to compound operators must be non empty, such that the type system can tell the type of the first item.

As shown above, an array literal works fine, but a mapped array needs special care.

fp-ts can be used to work with such ReadOnlyNonEmptyArrays.

interface ReadOnlyNonEmptyArray<A> extends ReadonlyArray<A> {
    0: A;
}
const array2 = pipe(
    [admins.selectStar(), users.selectStar()],
    RNEA.map((it) => it.selectStar())
);

intersect(array2).stringify();
SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      `admins` AS `adm`
  )
INTERSECT
SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      `users`
  )

This document used eval-md