Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

EXCEPT

View as MarkdownEdit this pageReport an issueSet up docs MCP

A cláusula EXCEPT retorna apenas as linhas produzidas pela primeira consulta e não pela segunda.

  • Ambas as consultas devem ter o mesmo número de colunas, na mesma ordem e com o mesmo tipo de dado.
  • O resultado de EXCEPT pode conter linhas duplicadas. Use EXCEPT DISTINCT se isso não for desejável.
  • Várias cláusulas EXCEPT são executadas da esquerda para a direita quando os parênteses não são especificados.
  • O operador EXCEPT tem a mesma prioridade que a cláusula UNION e prioridade menor que a cláusula INTERSECT.

Sintaxe

SELECT column1 [, column2 ]
FROM table1
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table2
[WHERE condition]

A condição pode ser qualquer expressão, dependendo dos seus requisitos.

Além disso, EXCEPT() pode ser usado para excluir colunas de um resultado na mesma tabela, como no BigQuery (Google Cloud), usando a seguinte sintaxe:

SELECT column1 [, column2 ] EXCEPT (column3 [, column4])
FROM table1
[WHERE condition]

Exemplos

Os exemplos desta seção demonstram o uso da cláusula EXCEPT.

Filtrando números usando a cláusula EXCEPT

Aqui está um exemplo simples que retorna os números de 1 a 10 que não estão entre 3 e 8:

Querysql
SELECT number
FROM numbers(1, 10)
EXCEPT
SELECT number
FROM numbers(3, 6)
Responseresponse
┌─number─┐
│      1 │
│      2 │
│      9 │
│     10 │
└────────┘

Excluindo colunas específicas com EXCEPT()

EXCEPT() pode ser usado para excluir rapidamente colunas de um resultado. Por exemplo, se quisermos selecionar todas as colunas de uma tabela, exceto algumas colunas específicas, como mostrado no exemplo abaixo:

Querysql
SHOW COLUMNS IN system.settings

SELECT * EXCEPT (default, alias_for, readonly, description)
FROM system.settings
LIMIT 5
Responseresponse
    ┌─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 │
   └─────────────────────────┴────────────┴─────────┴──────┴──────┴─────────┴─────────────┴────────────┘

Usando EXCEPT e INTERSECT com dados de criptomoedas

EXCEPT e INTERSECT muitas vezes podem ser usados de forma intercambiável, com lógicas booleanas diferentes, e ambos são úteis quando você tem duas tabelas que compartilham uma coluna (ou colunas). Por exemplo, suponha que tenhamos alguns milhões de linhas de dados históricos de criptomoedas contendo preços de negociação e volume:

Querysql
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;
Responseresponse
┌─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 │
└────────────┴─────────────┴─────────────┴──────────┴──────────────┴───────────────┘

Agora, suponha que temos uma tabela chamada holdings que contém uma lista das criptomoedas que possuímos, juntamente com a quantidade de moedas:

Querysql
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);

Podemos usar EXCEPT para responder a uma pergunta como "Quais moedas que temos nunca foram negociadas abaixo de US$ 10?":

Querysql
SELECT crypto_name FROM holdings
EXCEPT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;
Responseresponse
┌─crypto_name─┐
│ Bitcoin     │
│ Bitcoin     │
└─────────────┘

Isso significa que, das quatro criptomoedas que possuímos, apenas Bitcoin nunca caiu abaixo de $10 (com base nos dados limitados que temos aqui neste exemplo).

Usando EXCEPT DISTINCT

Observe que, na consulta anterior, havia várias posições em Bitcoin no resultado. Você pode adicionar DISTINCT a EXCEPT para eliminar linhas duplicadas do resultado:

Querysql
SELECT crypto_name FROM holdings
EXCEPT DISTINCT
SELECT crypto_name FROM crypto_prices
WHERE price < 10;
Responseresponse
┌─crypto_name─┐
│ Bitcoin     │
└─────────────┘

Veja também

Navigation