Skip to main content
Skip to main content
Edit this page

EXCEPT Clause

The EXCEPT clause returns only those rows that result from the first query without the second.

  • Both queries must have the same number of columns in the same order and data type.
  • The result of EXCEPT can contain duplicate rows. Use EXCEPT DISTINCT if this is not desirable.
  • Multiple EXCEPT statements are executed from left to right if parentheses are not specified.
  • The EXCEPT operator has the same priority as the UNION clause and lower priority than the INTERSECT clause.

Syntax​

The condition could be any expression based on your requirements.

Additionally, EXCEPT() can be used to exclude columns from a result in the same table, as is possible with BigQuery (Google Cloud), using the following syntax:

Examples​

The examples in this section demonstrate usage of the EXCEPT clause.

Filtering Numbers Using the EXCEPT Clause​

Here is a simple example that returns the numbers 1 to 10 that are not a part of the numbers 3 to 8:

Query:

Result:

Excluding Specific Columns Using EXCEPT()​

EXCEPT() can be used to quickly exclude columns from a result. For instance if we want to select all columns from a table, except a few select columns as shown in the example below:

Query:

Result:

Using EXCEPT and INTERSECT with Cryptocurrency Data​

EXCEPT and INTERSECT can often be used interchangeably with different Boolean logic, and they are both useful if you have two tables that share a common column (or columns). For example, suppose we have a few million rows of historical cryptocurrency data that contains trade prices and volume:

Query:

Result:

Now suppose we have a table named holdings that contains a list of cryptocurrencies that we own, along with the number of coins:

We can use EXCEPT to answer a question like "Which coins do we own have never traded below $10?":

Result:

This means of the four cryptocurrencies we own, only Bitcoin has never dropped below $10 (based on the limited data we have here in this example).

Using EXCEPT DISTINCT​

Notice in the previous query we had multiple Bitcoin holdings in the result. You can add DISTINCT to EXCEPT to eliminate duplicate rows from the result:

Result:

See Also