cláusula WHERE

Documentação da cláusula WHERE no ClickHouse

A cláusula WHERE permite filtrar os dados que vêm da cláusulaFROM de SELECT.

Se houver uma cláusula WHERE, ela deve ser seguida de uma expressão do tipo UInt8. As linhas para as quais essa expressão resulta em 0 são excluídas de transformações posteriores ou do resultado.

A expressão que segue a cláusula WHERE costuma ser usada com operadores de comparação e operadores lógicos, ou com uma das muitas funções regulares.

A expressão na cláusula WHERE é avaliada considerando a possibilidade de usar índices e poda de partições, se o engine de tabela subjacente oferecer suporte a isso.

PREWHERE

Também há uma otimização de filtragem chamada PREWHERE. PREWHERE é uma otimização para aplicar a filtragem com mais eficiência. Ela é habilitada por padrão mesmo que a cláusula PREWHERE não seja especificada explicitamente.

​

Verificar NULL

Se você precisar testar se um valor é NULL, use:

• IS NULL ou isNull
• IS NOT NULL ou isNotNull

Caso contrário, uma expressão com NULL nunca será avaliada como verdadeira.

​

Filtrando dados com operadores lógicos

Você pode usar as seguintes funções lógicas em conjunto com a cláusula WHERE para combinar várias condições:

• and() ou AND
• not() ou NOT
• or() ou NOT
• xor()

​

Usando colunas UInt8 como condição

No ClickHouse, colunas UInt8 podem ser usadas diretamente como condições booleanas, em que 0 é false e qualquer valor diferente de zero (geralmente 1) é true. Um exemplo disso é mostrado na seção abaixo.

​

Usando operadores de comparação

Os seguintes operadores de comparação podem ser usados:

Operador	Função	Descrição	Exemplo
a = b	equals(a, b)	Igual a	price = 100
a == b	equals(a, b)	Igual a (sintaxe alternativa)	price == 100
a != b	notEquals(a, b)	Diferente de	category != 'Electronics'
a <> b	notEquals(a, b)	Diferente de (sintaxe alternativa)	category <> 'Electronics'
a < b	less(a, b)	Menor que	price < 200
a <= b	lessOrEquals(a, b)	Menor que ou igual a	price <= 200
a > b	greater(a, b)	Maior que	price > 500
a >= b	greaterOrEquals(a, b)	Maior que ou igual a	price >= 500
a LIKE s	like(a, b)	Correspondência de padrões (diferencia maiúsculas de minúsculas)	name LIKE '%top%'
a NOT LIKE s	notLike(a, b)	Não corresponde ao padrão (diferencia maiúsculas de minúsculas)	name NOT LIKE '%top%'
a ILIKE s	ilike(a, b)	Correspondência de padrões (sem diferenciar maiúsculas de minúsculas)	name ILIKE '%LAPTOP%'
a BETWEEN b AND c	a >= b AND a <= c	Verificação de intervalo (inclusive)	price BETWEEN 100 AND 500
a NOT BETWEEN b AND c	a < b OR a > c	Verificação fora do intervalo	price NOT BETWEEN 100 AND 500

​

Correspondência de padrões e expressões condicionais

Além dos operadores de comparação, você pode usar correspondência de padrões e expressões condicionais na cláusula WHERE.

Recurso	Sintaxe	Sensível a maiúsculas e minúsculas	Desempenho	Melhor para
LIKE	col LIKE '%pattern%'	Sim	Rápido	Correspondência exata de padrão
ILIKE	col ILIKE '%pattern%'	Não	Mais lento	Busca sem diferenciar maiúsculas de minúsculas
if()	if(cond, a, b)	N/A	Rápido	Condições binárias simples
multiIf()	multiIf(c1, r1, c2, r2, def)	N/A	Rápido	Múltiplas condições
CASE	CASE WHEN ... THEN ... END	N/A	Rápido	Lógica condicional padrão SQL

Consulte “Correspondência de padrões e expressões condicionais” para ver exemplos de uso.

​

Expressão com literais, colunas ou subconsultas

A expressão após a cláusula WHERE também pode incluir literais, colunas ou subconsultas, que são instruções SELECT aninhadas que retornam valores usados em condições.

Tipo	Definição	Avaliação	Desempenho	Exemplo
Literal	Valor constante fixo	No momento da escrita da consulta	Mais rápido	WHERE price > 100
Coluna	Referência aos dados da tabela	Por linha	Rápido	WHERE price > cost
Subconsulta	SELECT aninhado	No momento da execução da consulta	Varia	WHERE id IN (SELECT ...)

Você pode combinar literais, colunas e subconsultas em condições complexas:

-- Literal + Coluna
WHERE price > 100 AND category = 'Electronics'

-- Coluna + Subconsulta
WHERE price > (SELECT AVG(price) FROM products) AND in_stock = true

-- Literal + Coluna + Subconsulta
WHERE category = 'Electronics' 
  AND price < 500
  AND id IN (SELECT product_id FROM bestsellers)

-- Todos os três com operadores lógicos
WHERE (price > 100 OR category IN (SELECT category FROM featured))
  AND in_stock = true
  AND name LIKE '%Special%'

​

Exemplos

​

Verificando NULL

Consultas com valores NULL:

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);

SELECT * FROM t_null WHERE y IS NULL;
SELECT * FROM t_null WHERE y != 0;

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘

​

Filtrando dados com operadores lógicos

Considere a tabela e os dados a seguir:

CREATE TABLE products (
    id UInt32,
    name String,
    price Float32,
    category String,
    in_stock Bool
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'Electronics', true),
(2, 'Mouse', 25.50, 'Electronics', true),
(3, 'Desk', 299.00, 'Furniture', false),
(4, 'Chair', 150.00, 'Furniture', true),
(5, 'Monitor', 350.00, 'Electronics', true),
(6, 'Lamp', 45.00, 'Furniture', false);

1. AND - ambas as condições devem ser atendidas:

SELECT * FROM products
WHERE category = 'Electronics' AND price < 500;

   ┌─id─┬─name────┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse   │  25.5 │ Electronics │ true     │
2. │  5 │ Monitor │   350 │ Electronics │ true     │
   └────┴─────────┴───────┴─────────────┴──────────┘

2. OR - pelo menos uma condição deve ser atendida:

SELECT * FROM products
WHERE category = 'Furniture' OR price > 500;

   ┌─id─┬─name───┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop │ 999.99 │ Electronics │ true     │
2. │  3 │ Desk   │    299 │ Furniture   │ false    │
3. │  4 │ Chair  │    150 │ Furniture   │ true     │
4. │  6 │ Lamp   │     45 │ Furniture   │ false    │
   └────┴────────┴────────┴─────────────┴──────────┘

3. NOT - Nega uma condição:

SELECT * FROM products
WHERE NOT in_stock;

   ┌─id─┬─name─┬─price─┬─category──┬─in_stock─┐
1. │  3 │ Desk │   299 │ Furniture │ false    │
2. │  6 │ Lamp │    45 │ Furniture │ false    │
   └────┴──────┴───────┴───────────┴──────────┘

4. XOR - Exatamente uma condição deve ser verdadeira (mas não ambas):

SELECT *
FROM products
WHERE xor(price > 200, category = 'Electronics')

   ┌─id─┬─name──┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse │  25.5 │ Electronics │ true     │
2. │  3 │ Desk  │   299 │ Furniture   │ false    │
   └────┴───────┴───────┴─────────────┴──────────┘

5. Combinando vários operadores:

SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Furniture')
  AND in_stock = true
  AND price < 400;

   ┌─id─┬─name────┬─price─┬─category────┬─in_stock─┐
1. │  2 │ Mouse   │  25.5 │ Electronics │ true     │
2. │  4 │ Chair   │   150 │ Furniture   │ true     │
3. │  5 │ Monitor │   350 │ Electronics │ true     │
   └────┴─────────┴───────┴─────────────┴──────────┘

6. Usando sintaxe de função:

SELECT * FROM products
WHERE and(or(category = 'Electronics', price > 100), in_stock);

   ┌─id─┬─name────┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop  │ 999.99 │ Electronics │ true     │
2. │  2 │ Mouse   │   25.5 │ Electronics │ true     │
3. │  4 │ Chair   │    150 │ Furniture   │ true     │
4. │  5 │ Monitor │    350 │ Electronics │ true     │
   └────┴─────────┴────────┴─────────────┴──────────┘

A sintaxe com palavras-chave SQL (AND, OR, NOT, XOR) geralmente é mais legível, mas a sintaxe de função pode ser útil em expressões complexas ou ao criar consultas dinâmicas.

​

Usando colunas UInt8 como condição

Usando a tabela de um exemplo anterior, você pode usar o nome de uma coluna diretamente como condição:

SELECT * FROM products
WHERE in_stock

   ┌─id─┬─name────┬──price─┬─category────┬─in_stock─┐
1. │  1 │ Laptop  │ 999.99 │ Electronics │ true     │
2. │  2 │ Mouse   │   25.5 │ Electronics │ true     │
3. │  4 │ Chair   │    150 │ Furniture   │ true     │
4. │  5 │ Monitor │    350 │ Electronics │ true     │
   └────┴─────────┴────────┴─────────────┴──────────┘

​

Usando operadores de comparação

Os exemplos abaixo usam a tabela e os dados do exemplo acima. Os resultados foram omitidos por brevidade.

1. Igualdade explícita com true (= 1 ou = true):

SELECT * FROM products
WHERE in_stock = true;
-- ou
WHERE in_stock = 1;

2. Igualdade explícita com false (= 0 ou = false):

SELECT * FROM products
WHERE in_stock = false;
-- ou
WHERE in_stock = 0;

3. Desigualdade (!= 0 ou != false):

SELECT * FROM products
WHERE in_stock != false;
-- ou
WHERE in_stock != 0;

4. Maior que:

SELECT * FROM products
WHERE in_stock > 0;

5. Menor ou igual a:

SELECT * FROM products
WHERE in_stock <= 0;

6. Em combinação com outras condições:

SELECT * FROM products
WHERE in_stock AND price < 400;

7. Usando o operador IN:

No exemplo abaixo, (1, true) é uma tupla.

SELECT * FROM products
WHERE in_stock IN (1, true);

Você também pode usar um array para isso:

SELECT * FROM products
WHERE in_stock IN [1, true];

8. Mistura de estilos de comparação:

SELECT * FROM products
WHERE category = 'Electronics' AND in_stock = true;

​

Correspondência de padrões e expressões condicionais

Os exemplos abaixo usam a tabela e os dados do exemplo acima. Os resultados foram omitidos por brevidade.

​

Exemplos de LIKE

-- Encontrar produtos com 'o' no nome
SELECT * FROM products WHERE name LIKE '%o%';
-- Resultado: Laptop, Monitor

-- Encontrar produtos que começam com 'L'
SELECT * FROM products WHERE name LIKE 'L%';
-- Resultado: Laptop, Lamp

-- Encontrar produtos com exatamente 4 caracteres
SELECT * FROM products WHERE name LIKE '____';
-- Resultado: Desk, Lamp

​

Exemplos de ILIKE

-- Busca sem distinção entre maiúsculas e minúsculas por 'LAPTOP'
SELECT * FROM products WHERE name ILIKE '%laptop%';
-- Result: Laptop

-- Correspondência de prefixo sem distinção entre maiúsculas e minúsculas
SELECT * FROM products WHERE name ILIKE 'l%';
-- Result: Laptop, Lamp

​

Exemplos de IF

-- Diferentes limites de preço por categoria
SELECT * FROM products
WHERE if(category = 'Electronics', price < 500, price < 200);
-- Resultado: Mouse, Chair, Monitor
-- (Electronics abaixo de $500 OU Furniture abaixo de $200)

-- Filtrar com base no status do estoque
SELECT * FROM products
WHERE if(in_stock, price > 100, true);
-- Resultado: Laptop, Chair, Monitor, Desk, Lamp
-- (Itens em estoque acima de $100 OU todos os itens fora de estoque)

​

Exemplos de multiIf

-- Múltiplas condições baseadas em categoria
SELECT * FROM products
WHERE multiIf(
    category = 'Electronics', price < 600,
    category = 'Furniture', in_stock = true,
    false
);
-- Resultado: Mouse, Monitor, Chair
-- (Electronics < $600 OU Furniture em estoque)

-- Filtragem em camadas
SELECT * FROM products
WHERE multiIf(
    price > 500, category = 'Electronics',
    price > 100, in_stock = true,
    true
);
-- Resultado: Laptop, Chair, Monitor, Lamp

​

Exemplos de CASE

CASE simples:

-- Regras diferentes por categoria
SELECT * FROM products
WHERE CASE category
    WHEN 'Electronics' THEN price < 400
    WHEN 'Furniture' THEN in_stock = true
    ELSE false
END;
-- Resultado: Mouse, Monitor, Chair

CASE com condição:

-- Lógica de níveis baseada em preço
SELECT * FROM products
WHERE CASE
    WHEN price > 500 THEN in_stock = true
    WHEN price > 100 THEN category = 'Electronics'
    ELSE true
END;
-- Resultado: Laptop, Monitor, Mouse, Lamp