Skip to content

Combining queries

SQL lets you combine the results of multiple SELECT statements using the UNION, INTERSECT, and EXCEPT clauses.

SQL Set operations

The SQL Set operations are provided as the union_, intersect_, and except_ functions. SQL also allows an optional ALL clause to be specified with each of these. Beam implements these as unionAll_, intersectAll_, and exceptAll_ respectively. Each combinator takes two queries as arguments. The results of both queries will be combined accordingly. The returned type is the same as the type of both query arguments, which must be the same.

For example, suppose we wanted the first and last names of both customers and employees.

let customerNames =
      fmap (\c -> (customerFirstName c, customerLastName c))
           (all_ (customer chinookDb))
    employeeNames =
      fmap (\e -> (employeeFirstName e, employeeLastName e))
           (all_ (employee chinookDb))
in union_ customerNames employeeNames

  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Customer" AS "t0")
UNION
  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Employee" AS "t0")

SELECT "t0"."FirstName" AS "res0",
       "t0"."LastName" AS "res1"
FROM "Customer" AS "t0"
UNION
SELECT "t0"."FirstName" AS "res0",
       "t0"."LastName" AS "res1"
FROM "Employee" AS "t0";

-- With values: []

Combining arbitrary set expressions

Suppose we wanted all employee and customer first names that were also customer last names but not also employee last names. We could use UNION to combine the results of a query over the first names of employees and customers, and an EXCEPT to get all customer last names that were not employee ones. Finally, an INTERSECT would give us the result we want. The beam query language allows this and many popular backends do as well, but standard SQL makes it difficult to express. Beam has decided to go with the most common implement solution, which is to allow such nesting. This simplifies the API design.

On backends which allow such nesting (like Postgres), the query is translated directly. On backends that do not (like SQLite), an appropriate subselect is generated.

let customerFirstNames =
      fmap customerFirstName
           (all_ (customer chinookDb))
    employeeFirstNames =
      fmap employeeFirstName
           (all_ (employee chinookDb))
    customerLastNames =
      fmap customerLastName
           (all_ (customer chinookDb))
    employeeLastNames =
      fmap employeeLastName
           (all_ (employee chinookDb))
in (customerFirstNames `union_`employeeFirstNames) `intersect_`
   (customerLastNames `except_` employeeLastNames)

(
   (SELECT "t0"."FirstName" AS "res0"
    FROM "Customer" AS "t0")
 UNION
   (SELECT "t0"."FirstName" AS "res0"
    FROM "Employee" AS "t0")) INTERSECT (
                                           (SELECT "t0"."LastName" AS "res0"
                                            FROM "Customer" AS "t0")
                                         EXCEPT
                                           (SELECT "t0"."LastName" AS "res0"
                                            FROM "Employee" AS "t0"))

SELECT "t0"."res0" AS "res0"
FROM
  (SELECT "t0"."FirstName" AS "res0"
   FROM "Customer" AS "t0"
   UNION SELECT "t0"."FirstName" AS "res0"
   FROM "Employee" AS "t0") AS "t0" INTERSECT
SELECT "t0"."res0" AS "res0"
FROM
  (SELECT "t0"."LastName" AS "res0"
   FROM "Customer" AS "t0"
   EXCEPT SELECT "t0"."LastName" AS "res0"
   FROM "Employee" AS "t0") AS "t0";

-- With values: []

LIMIT/OFFSET and set operations

The LIMIT and OFFSET clauses generated by limit_ and offset_ apply to the entire result of the set operation. Beam will correctly generate the query you specify, placing the LIMIT and OFFSET at the appropriate point. If necessary, it will also generate a sub select to preserve the meaning of the query.

For example, to get the second ten full names in common.

let customerNames =
      fmap (\c -> (customerFirstName c, customerLastName c))
           (all_ (customer chinookDb))
    employeeNames =
      fmap (\e -> (employeeFirstName e, employeeLastName e))
           (all_ (employee chinookDb))
in limit_ 10 (union_ customerNames employeeNames)

  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Customer" AS "t0")
UNION
  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Employee" AS "t0")
LIMIT 10

SELECT "t0"."FirstName" AS "res0",
       "t0"."LastName" AS "res1"
FROM "Customer" AS "t0"
UNION
SELECT "t0"."FirstName" AS "res0",
       "t0"."LastName" AS "res1"
FROM "Employee" AS "t0"
LIMIT 10;

-- With values: []

If we only wanted the union of the first 10 names of each.

let customerNames =
      fmap (\c -> (customerFirstName c, customerLastName c))
           (all_ (customer chinookDb))
    employeeNames =
      fmap (\e -> (employeeFirstName e, employeeLastName e))
           (all_ (employee chinookDb))
in union_ (limit_ 10 customerNames) (limit_ 10 employeeNames)

  (SELECT "t0"."res0" AS "res0",
          "t0"."res1" AS "res1"
   FROM
     (SELECT "t0"."FirstName" AS "res0",
             "t0"."LastName" AS "res1"
      FROM "Customer" AS "t0"
      LIMIT 10) AS "t0")
UNION
  (SELECT "t0"."res0" AS "res0",
          "t0"."res1" AS "res1"
   FROM
     (SELECT "t0"."FirstName" AS "res0",
             "t0"."LastName" AS "res1"
      FROM "Employee" AS "t0"
      LIMIT 10) AS "t0")

SELECT "t0"."res0" AS "res0",
       "t0"."res1" AS "res1"
FROM
  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Customer" AS "t0"
   LIMIT 10) AS "t0"
UNION
SELECT "t0"."res0" AS "res0",
       "t0"."res1" AS "res1"
FROM
  (SELECT "t0"."FirstName" AS "res0",
          "t0"."LastName" AS "res1"
   FROM "Employee" AS "t0"
   LIMIT 10) AS "t0";

-- With values: []