Set Operators

Set operators combine two input tables produced by any SQL operator using set union, set intersection, and set subtraction.

A set operation has the form

<sql-op> UNION [ALL | DISTINCT] <sql-op>
<sql-op> INTERSECT [ALL | DISTINCT] <sql-op>
<sql-op> EXCLUDE [ALL | DISTINCT] <sql-op>

where <sql-op> is any SQL operator.

The set operators all have equal precedence and associate left to right. Parentheses may be used to override the default left-to-right evaluation order.

The table produced by the first <sql-op> is called the left table and the table produced by the other <sql-op> is called the right table.

Note

Only the UNION set operator is currently supported. The INTERSECT AND EXCLUDE operators will be available in a future version of SuperSQL.

UNION

The UNION operation performs a relational set union between the left and right tables.

The number of columns in the two tables must be the same but the column names need not match. The output table inherits the column names of the left table and the columns from the right table are merged into the output based on column position not by name.

If the ALL keyword is present, then all rows from both tables are included in the output.

If the DISTINCT keyword is present, then only unique rows are included in the output.

If neither the ALL nor DISTINCT keywords are is present, then DISTINCT is presumed.

Non-relational Data

When processing mixed-type tables or non-table inputs, the effect of union can be achieved by simply combining pipe queries using fork.

When it is desirable to have a homogenous output for such data, data can be fused into one type with the fuse operator, which resembles the union-by-name variation available in some SQL dialects.

Examples

Basic union where column name inherited from left table

# spq
SELECT 1 as x
UNION
SELECT 2 as y
ORDER BY x
# input

# expected output
{x:1}
{x:2}

UNION results are distinct by default

# spq
SELECT 1 as x
UNION
SELECT 2 as y
UNION
SELECT 2 as z
ORDER BY x
# input

# expected output
{x:1}
{x:2}

UNION ALL retains duplicate rows

# spq
SELECT 1 as x
UNION ALL
SELECT 2 as y
UNION ALL
SELECT 2 as z
ORDER BY x
# input

# expected output
{x:1}
{x:2}
{x:2}

Misaligned tables cause a compilation error

# spq
WITH T(x,y) AS (
    VALUES (1,2), (3,4), (5,6)
),
U(z) AS (
    VALUES (2), (3)
)
SELECT * FROM T
UNION ALL
SELECT * from U
# input

# expected output
set operations can only be applied to sources with the same number of columns at line 7, column 1:
SELECT * FROM T
~~~~~~~~~~~~~~~

Pad a table to align columns

# spq
WITH T(x,y) AS (
    VALUES (1,2), (3,4), (5,6)
),
U(z) AS (
    VALUES (2), (3)
)
SELECT * FROM T
UNION ALL
SELECT *, 0 from U
ORDER BY x,y
# input

# expected output
{x:1,y:2}
{x:2,y:0}
{x:3,y:0}
{x:3,y:4}
{x:5,y:6}

Fuse data as an alternative to a SQL UNION

# spq
fork
  (
    WITH T(x,y) AS (
      VALUES (1,2), (3,4), (5,6)
    )
    SELECT * FROM T
  )
  (
    WITH U(z) AS (
      VALUES (2), (3)
    )
    SELECT * FROM U
  )
| sort x,z
| fuse
# input

# expected output
{x:1,y:2,z:null::int64}
{x:3,y:4,z:null::int64}
{x:5,y:6,z:null::int64}
{x:null::int64,y:null::int64,z:2}
{x:null::int64,y:null::int64,z:3}