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
EXCEPTcan contain duplicate rows. UseEXCEPT DISTINCTif this is not desirable. - Multiple
EXCEPTstatements are executed from left to right if parentheses are not specified. - The
EXCEPToperator has the same priority as theUNIONclause and lower priority than theINTERSECTclause.
Syntax
SELECT column1 [, column2 ]
FROM table1
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table2
[WHERE condition]
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:
SELECT column1 [, column2 ] EXCEPT (column3 [, column4])
FROM table1
[WHERE condition]
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:
SELECT number
FROM numbers(1, 10)
EXCEPT
SELECT number
FROM numbers(3, 6)
Result:
┌─number─┐
│ 1 │
│ 2 │
│ 9 │
│ 10 │
└────────┘
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:
SHOW COLUMNS IN system.settings
SELECT * EXCEPT (default, alias_for, readonly, description)
FROM system.settings
LIMIT 5
Result:
┌─field───────┬─type─────────────────────────────────────────────────────────────────────┬─null─┬─key─┬─default─┬─extra─┐
1. │ alias_for │ String │ NO │ │ ᴺᵁᴸᴸ │ │
2. │ changed │ UInt8 │ NO │ │ ᴺᵁᴸᴸ │ │
3. │ default │ String │ NO │ │ ᴺᵁᴸᴸ │ │
4. │ description │ String │ NO │ │ ᴺᵁᴸᴸ │ │
5. │ is_obsolete │ UInt8 │ NO │ │ ᴺᵁᴸᴸ │ │
6. │ max │ Nullable(String) │ YES │ │ ᴺᵁᴸᴸ │ │
7. │ min │ Nullable(String) │ YES │ │ ᴺᵁᴸᴸ │ │
8. │ name │ String │ NO │ │ ᴺᵁᴸᴸ │ │
9. │ readonly │ UInt8 │ NO │ │ ᴺᵁᴸᴸ │ │
10. │ tier │ Enum8('Production' = 0, 'Obsolete' = 4, 'Experimental' = 8, 'Beta' = 12) │ NO │ │ ᴺᵁᴸᴸ │ │
11. │ type │ String │ NO │ │ ᴺᵁᴸᴸ │ │
12. │ value │ String │ NO │ │ ᴺᵁᴸᴸ │ │
└─────────────┴──────────────────────────────────────────────────────────────────────────┴──────┴─────┴─────────┴───────┘
┌─name────────────────────┬─value──────┬─changed─┬─min──┬─max──┬─type────┬─is_obsolete─┬─tier───────┐
1. │ dialect │ clickhouse │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Dialect │ 0 │ Production │
2. │ min_compress_block_size │ 65536 │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64 │ 0 │ Production │
3. │ max_compress_block_size │ 1048576 │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64 │ 0 │ Production │
4. │ max_block_size │ 65409 │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64 │ 0 │ Production │
5. │ max_insert_block_size │ 1048449 │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ UInt64 │ 0 │ Production │
└─────────────────────────┴────────────┴─────────┴──────┴──────┴─────────┴─────────────┴────────────┘
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:
CREATE TABLE crypto_prices
(
trade_date Date,
crypto_name String,
volume Float32,
price Float32,
market_cap Float32,
change_1_day Float32
)
ENGINE = MergeTree
PRIMARY KEY (crypto_name, trade_date);
INSERT INTO crypto_prices
SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/crypto_prices.csv',
'CSVWithNames'
);
SELECT * FROM crypto_prices
WHERE crypto_name = 'Bitcoin'
ORDER BY trade_date DESC
LIMIT 10;
Result:
┌─trade_date─┬─crypto_name─┬──────volume─┬────price─┬───market_cap─┬──change_1_day─┐
│ 2020-11-02 │ Bitcoin │ 30771456000 │ 13550.49 │ 251119860000 │ -0.013585099 │
│ 2020-11-01 │ Bitcoin │ 24453857000 │ 13737.11 │ 254569760000 │ -0.0031840964 │
│ 2020-10-31 │ Bitcoin │ 30306464000 │ 13780.99 │ 255372070000 │ 0.017308505 │
│ 2020-10-30 │ Bitcoin │ 30581486000 │ 13546.52 │ 251018150000 │ 0.008084608 │
│ 2020-10-29 │ Bitcoin │ 56499500000 │ 13437.88 │ 248995320000 │ 0.012552661 │
│ 2020-10-28 │ Bitcoin │ 35867320000 │ 13271.29 │ 245899820000 │ -0.02804481 │
│ 2020-10-27 │ Bitcoin │ 33749879000 │ 13654.22 │ 252985950000 │ 0.04427984 │
│ 2020-10-26 │ Bitcoin │ 29461459000 │ 13075.25 │ 242251000000 │ 0.0033826586 │
│ 2020-10-25 │ Bitcoin │ 24406921000 │ 13031.17 │ 241425220000 │ -0.0058658565 │
│ 2020-10-24 │ Bitcoin │ 24542319000 │ 13108.06 │ 242839880000 │ 0.013650347 │
└────────────┴─────────────┴─────────────┴──────────┴──────────────┴───────────────┘
Now suppose we have a table named holdings that contains a list of cryptocurrencies that we own, along with the number of coins:
CREATE TABLE holdings
(
crypto_name String,
quantity UInt64
)
ENGINE = MergeTree
PRIMARY KEY (crypto_name);
INSERT INTO holdings VALUES
('Bitcoin', 1000),
('Bitcoin', 200),
('Ethereum', 250),
('Ethereum', 5000),
('DOGEFI', 10),
('Bitcoin Diamond', 5000);
We can use EXCEPT to answer a question like "Which coins do we own have never traded below $10?":
SELECT crypto_name FROM holdings
EXCEPT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;
Result:
┌─crypto_name─┐
│ Bitcoin │
│ Bitcoin │
└─────────────┘
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:
SELECT crypto_name FROM holdings
EXCEPT DISTINCT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;
Result:
┌─crypto_name─┐
│ Bitcoin │
└─────────────┘
See Also