UNION, INTERSECT, and EXCEPT

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

Syntax

SELECT query1 UNION query2
|
SELECT query1 INTERSECT query2
|
SELECT query1 EXCEPT query2

Arguments

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.
query1, query2: Strings or expressions that represent valid SELECT statements.

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.

Examples

For the following examples, consider these queries and results.

Q1: SELECT DISTINCT city FROM `travel-sample` WHERE type = "airport";      (1641 results)

Q2: SELECT DISTINCT city FROM `travel-sample` WHERE type = "hotel";        (274 results)

Example 1: UNION of Q1 and Q2.

SELECT DISTINCT city FROM `travel-sample` WHERE type = "airport"
UNION SELECT DISTINCT city FROM `travel-sample` WHERE type = "hotel";

This gives 1871 results:

[
  {
    "city": "Calais"
  },
  {
    "city": "Peronne"
  },
  {
    "city": "Nangis"
  },
  {
    "city": "Bagnole-de-l'orne"
  },
...

Example 2: INTERSECT of Q1 and Q2.

SELECT DISTINCT city FROM `travel-sample` WHERE type = "airport"
INTERSECT SELECT DISTINCT city FROM `travel-sample` WHERE type = "hotel";

This gives 44 results:

[
  {
    "city": "Cannes"
  },
  {
    "city": "Nice"
  },
  {
    "city": "Orange"
  },
  {
    "city": "Avignon"
  },
...

Example 3: EXCEPT of Q1 and Q2.

SELECT DISTINCT city FROM `travel-sample` WHERE type = "airport"
EXCEPT SELECT DISTINCT city FROM `travel-sample` WHERE type = "hotel";

This gives 1597 results:

[
  {
    "city": "Calais"
  },
  {
    "city": "Peronne"
  },
  {
    "city": "Nangis"
  },
  {
    "city": "Bagnole-de-l'orne"
  },
...

Example 4: EXCEPT of Q2 and Q1.

SELECT DISTINCT city FROM `travel-sample` WHERE type = "hotel"
UNION SELECT DISTINCT city FROM `travel-sample` WHERE type = "airport";

This gives 230 results:

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