A newer version of this documentation is available.

View Latest

UNION, INTERSECT, and EXCEPT

  • reference
    +
    The set operators UNION, INTERSECT, and EXCEPT combine the resultsets of two or more SELECT statements.

    Syntax

    select-set ::= select-term [ set-operator [ ALL ] select-term ]*
    select-term ( set-operator 'ALL' select-term )*
    set-operator ::= UNION | INTERSECT | EXCEPT
    'UNION' | 'INTERSECT'| 'EXCEPT'
    select-term

    A string or expression that represents a valid SELECT statement.

    UNION

    Returns all values from both the first and second SELECT statements.

    INTERSECT

    Returns only values present in both the first and second SELECT statements.

    EXCEPT

    Returns values from the first SELECT statement that are absent from the second SELECT statement.

    Return Values

    UNION, INTERSECT, and EXCEPT return distinct results, such that there are no duplicates.

    UNION ALL, INTERSECT ALL, and EXCEPT ALL return all applicable values, including duplicates. These queries are faster, because they do not compute distinct results.

    You can improve the performance of a query by using covering indexes, where the index includes all the information needed to satisfy the query. For more information, see Covering Indexes.

    To order all the results of a set operator together, refer to the examples for the ORDER BY clause.

    Examples

    For the following examples, consider these queries and results.

    Q1
    SELECT DISTINCT city FROM `travel-sample`.inventory.airport;

    (1641 results)

    Q2
    SELECT DISTINCT city FROM `travel-sample`.inventory.hotel;

    (274 results)

    The SELECT statements in the following examples do not need to use the DISTINCT keyword, since the set operators return distinct results when used without the ALL keyword.
    Example 1. UNION of Q1 and Q2
    SELECT city FROM `travel-sample`.inventory.airport
    UNION
    SELECT city FROM `travel-sample`.inventory.hotel;

    This gives 1871 results:

    [
      {
        "city": "Calais"
      },
      {
        "city": "Peronne"
      },
      {
        "city": "Nangis"
      },
      {
        "city": "Bagnole-de-l'orne"
      },
    ...
    ]
    Example 2. INTERSECT of Q1 and Q2
    SELECT city FROM `travel-sample`.inventory.airport
    INTERSECT
    SELECT city FROM `travel-sample`.inventory.hotel;

    This gives 44 results:

    [
      {
        "city": "Cannes"
      },
      {
        "city": "Nice"
      },
      {
        "city": "Orange"
      },
      {
        "city": "Avignon"
      },
    ...
    ]
    Example 3. EXCEPT of Q1 and Q2
    SELECT city FROM `travel-sample`.inventory.airport
    EXCEPT
    SELECT city FROM `travel-sample`.inventory.hotel;

    This gives 1597 results:

    [
      {
        "city": "Calais"
      },
      {
        "city": "Peronne"
      },
      {
        "city": "Nangis"
      },
      {
        "city": "Bagnole-de-l'orne"
      },
    ...
    ]
    Example 4. EXCEPT of Q2 and Q1
    SELECT city FROM `travel-sample`.inventory.hotel
    EXCEPT
    SELECT city FROM `travel-sample`.inventory.airport;

    This gives 230 results:

    [
      {
        "city": "Medway"
      },
      {
        "city": "Gillingham"
      },
      {
        "city": "Giverny"
      },
      {
        "city": "Highland"
      },
    ...
    ]