BETWEEN, IN e LIKE: filtros mais curtos

Operadores de condição práticos

Os restauradores da velha Terra recompunham livros a partir de um fragmento do desenho da encadernação. Nem sempre precisavam do título exato. Às vezes bastava um indício: começa com tal letra, pertence a tal seção, está em tal faixa de datas.

O arquivista de dados trabalha de um jeito parecido.

No WHERE, nem sempre é confortável escrever um monte de condições ligadas por AND e OR. Às vezes a ideia fica mais simples com um operador curto:

price >= 1000 AND price <= 3000

pode ser escrito assim:

price BETWEEN 1000 AND 3000

E uma sequência longa como:

category = 'Книги'
OR category = 'Игрушки'
OR category = 'Аксессуары'

pode ser trocada por:

category IN ('Книги', 'Игрушки', 'Аксессуары')

E, se você precisa encontrar os itens cujo nome começa com a palavra «Café», dá para usar um padrão:

name LIKE 'Кофе%'

Nesta aula há três ferramentas principais:

• BETWEEN a AND b — o valor cai no intervalo de a até b;
• IN (...) — o valor está na lista;
• LIKE 'padrão' — o texto se encaixa num padrão de texto.

Nenhum dos três operadores faz mágica. Eles só ajudam a escrever condições mais curtas e mais claras.

QUERY: Um bom filtro é como uma ordem precisa para um drone batedor: não «procure alguma coisa útil», e sim «mostre os itens destas categorias, nesta faixa, com o nome começando assim».

Três modelos diferentes de busca

Esses operadores têm papéis diferentes.

O BETWEEN responde à pergunta:

O valor está entre dois limites?

Por exemplo:

price BETWEEN 1000 AND 3000

Lê-se assim:

preço de 1000 a 3000, inclusive.

O IN responde à pergunta:

O valor está neste conjunto de opções?

Por exemplo:

category IN ('Книги', 'Игрушки')

Lê-se assim:

a categoria é Livros ou Brinquedos.

O LIKE responde à pergunta:

O texto se parece com este padrão?

Por exemplo:

name LIKE 'Кофе%'

Lê-se assim:

o nome começa com "Café".

Ou seja:

BETWEEN: um valor entre dois limites

O BETWEEN verifica se um valor está dentro de um intervalo.

Digamos que você precise encontrar os itens com preço de 1000 a 3000:

SELECT name, price
FROM products
WHERE price BETWEEN 1000 AND 3000;

É o mesmo que escrever:

SELECT name, price
FROM products
WHERE price >= 1000
  AND price <= 3000;

O detalhe principal:

O BETWEEN inclui os dois limites.

Ou seja, a condição:

price BETWEEN 1000 AND 3000

devolve os itens com preço:

• 1000
• 1500
• 2999
• 3000

O preço 1000 entra.
O preço 3000 também entra.

Se você precisa de valores estritamente maiores que 1000 e estritamente menores que 3000, o BETWEEN não serve. Nesse caso, escreva as comparações comuns:

WHERE price > 1000
  AND price < 3000

Detalhes importantes do BETWEEN

1. Os limites não se invertem sozinhos

Este filtro está correto:

WHERE price BETWEEN 1000 AND 3000

Já este quase sempre devolve um resultado vazio:

WHERE price BETWEEN 3000 AND 1000

porque o banco lê isso como:

WHERE price >= 3000
  AND price <= 1000

Nenhum número comum consegue ser maior ou igual a 3000 e menor ou igual a 1000 ao mesmo tempo.

Por isso a ordem dos limites importa: primeiro o inferior, depois o superior.

2. Existe a forma inversa, NOT BETWEEN

Se você precisa encontrar os itens fora do intervalo, pode escrever:

SELECT name, price
FROM products
WHERE price NOT BETWEEN 1000 AND 3000;

Isso é parecido com:

WHERE price < 1000
   OR price > 3000

Ou seja, o item de 500 entra.
O item de 4000 entra.
O item de 1000 não entra.
O item de 3000 não entra.

Porque os limites fazem parte do próprio intervalo do BETWEEN.

3. Com datas e horários é preciso mais cuidado

O BETWEEN também funciona com datas:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

Mas, se created_at não for só uma data, e sim uma data com hora, por exemplo:

2024-01-31 18:45:00

esse filtro pode acabar deixando de fora quase todo o último dia.

Por quê? Porque, para um timestamp, '2024-01-31' costuma significar o começo do dia:

2024-01-31 00:00:00

Por isso, quando o período envolve horário, costuma ser mais seguro escrever um intervalo semiaberto:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Assim pegamos tudo a partir de 1º de janeiro, inclusive, e tudo até 1º de fevereiro, sem incluir 1º de fevereiro.

Para quem está começando, a regra principal é esta:

Para números, o BETWEEN é prático e claro. Para datas com hora, confira os limites com atenção redobrada.

IN: o valor está na lista

O IN é usado quando você precisa testar um valor contra várias opções possíveis.

Digamos que você precise encontrar só os itens das categorias "Livros" e "Brinquedos":

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки');

Isso é mais curto e mais claro do que:

SELECT name, category, price
FROM products
WHERE category = 'Книги'
   OR category = 'Игрушки';

O IN se lê assim:

a categoria está na lista: Livros, Brinquedos.

O IN fica especialmente útil quando há muitas opções:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары', 'Корм', 'Домики')

Em vez de uma longa sequência de OR, sai um único filtro compacto.

Detalhes importantes do IN

1. O IN é prático para uma lista de valores exatos

O IN não procura valores parecidos. Ele verifica a correspondência exata com uma das opções.

WHERE category IN ('Книги', 'Игрушки')

Vai encontrar a categoria 'Книги'.

Mas não vai encontrar:

Книга
книги
КНИГИ
Электронные книги

porque essas já são outras strings.

2. O NOT IN exclui os valores da lista

Se você precisa tirar algumas categorias, pode escrever:

SELECT name, category, price
FROM products
WHERE category NOT IN ('Книги', 'Игрушки');

Lê-se assim:

mostre os itens cuja categoria não está na lista "Livros", "Brinquedos".

Mas o NOT IN tem uma armadilha importante com NULL. Ela vem logo abaixo, num bloco separado.

3. O IN combina bem com o BETWEEN

Por exemplo:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000
ORDER BY price;

Lê-se assim:

mostre livros e brinquedos com preço de 1000 a 3000.

Esse já é um filtro completo de verdade: uma lista de categorias mais um intervalo de preço.

LIKE: busca por padrão de texto

O LIKE é usado quando você precisa encontrar um texto não pela correspondência exata, mas por um padrão.

Digamos que você precise encontrar os itens cujo nome começa com a palavra "Café":

SELECT name, price
FROM products
WHERE name LIKE 'Кофе%';

O sinal % significa:

qualquer quantidade de caracteres, sejam quais forem.

Inclusive nenhum caractere.

Por isso o padrão:

'Кофе%'

vai encontrar:

Кофе
Кофе молотый
Кофейная станция
Кофе для котонавтов

Mas não vai encontrar:

Большой кофе
Набор: кофе и кружка

porque o padrão exige que o texto comece por Кофе.

%: qualquer quantidade de caracteres

O % é o caractere mais frequente no LIKE.

Ele significa:

aqui pode haver qualquer quantidade de caracteres.

Veja os padrões a seguir.

Começa com um texto

WHERE name LIKE 'Кофе%'

Vai encontrar os nomes que começam por Кофе.

Exemplos de correspondências:

Кофе
Кофе молотый
Кофейный набор

Termina com um texto

WHERE name LIKE '%кофе'

Vai encontrar os nomes que terminam em кофе.

Exemplos de correspondências:

Большой кофе
Капсульный кофе
Набор для кофе

Contém o texto em qualquer lugar

WHERE name LIKE '%кофе%'

Vai encontrar os nomes em que кофе aparece em qualquer posição.

Exemplos de correspondências:

кофе
Большой кофе
Набор для кофе и чая
Свежие кофейные зёрна

Já o exemplo com letra maiúscula da primeira lista não aparece aqui: o LIKE diferencia maiúsculas de minúsculas, e К e к são caracteres diferentes. Vamos falar mais sobre maiúsculas e minúsculas logo adiante nesta aula.

Em linguagem figurada:

'Кофе%'

— o texto precisa começar com essa sequência.

'%кофе'

— o texto precisa terminar com essa sequência.

'%кофе%'

— o texto precisa conter essa sequência em algum lugar.

_: exatamente um caractere

Além do %, o LIKE tem um segundo caractere especial:

_

Ele significa:

exatamente um caractere qualquer.

Por exemplo:

WHERE code LIKE 'A_1'

Servem:

AB1
AX1
A71

Mas não servem:

A1
ABCD1
AA21

Por quê?

O padrão A_1 exige:

1. primeiro A
2. depois exatamente um caractere qualquer
3. depois 1

Mais um exemplo:

WHERE name LIKE 'Кот_'

Servem as strings de quatro caracteres:

Коты
Котя
Кот1

Mas não serve:

Кот
Котик

Porque o _ exige exatamente um caractere a mais.

A diferença é esta:

Maiúsculas e minúsculas: LIKE, ILIKE e LOWER

No PostgreSQL, o LIKE comum diferencia maiúsculas de minúsculas.

Isso quer dizer que o padrão:

WHERE name LIKE 'смарт%'

pode não encontrar esta linha:

Смарт-часы

porque с e С são caracteres diferentes.

Para buscar sem diferenciar maiúsculas de minúsculas, o PostgreSQL tem o ILIKE:

SELECT name, price
FROM products
WHERE name ILIKE 'смарт%';

O ILIKE é a variante do LIKE que ignora maiúsculas e minúsculas.

Ele vai encontrar:

смарт-часы
Смарт-часы
СМАРТ-ЧАСЫ

Mas vale lembrar: o ILIKE é uma facilidade do PostgreSQL, não uma parte universal do SQL padrão.

Uma abordagem mais portável é passar todo o texto para a mesma caixa:

SELECT name, price
FROM products
WHERE LOWER(name) LIKE 'смарт%';

Aqui o banco primeiro converte name para minúsculas e só depois compara com o padrão escrito em minúsculas.

Também dá para fazer assim:

WHERE LOWER(name) LIKE LOWER('Смарт%')

Mas, na prática, o padrão já costuma ser escrito direto em minúsculas:

WHERE LOWER(name) LIKE 'смарт%'

Como buscar os próprios caracteres % e _

O LIKE tem um problema: % e _ são caracteres especiais.

% significa qualquer quantidade de caracteres.
_ significa exatamente um caractere.

Mas às vezes você precisa encontrar o sinal de porcentagem ou o sublinhado como texto comum.

Por exemplo, imagine estes itens:

Скидка 50%
Корм 50 кг
QA_набор
QA-набор

Se você escrever:

WHERE name LIKE '50%'

isso não quer dizer "encontre o texto 50%".

Quer dizer:

encontre as linhas que começam com 50 e seguem com qualquer coisa.

Para dizer ao banco "este % é um sinal de porcentagem comum", usa-se o ESCAPE.

Por exemplo:

SELECT name
FROM products
WHERE name LIKE '%50!%%' ESCAPE '!';

Vamos destrinchar o padrão:

'%50!%%'

• o primeiro % — qualquer começo de texto
• 50 — caracteres comuns
• !% — um sinal de porcentagem comum, porque ! foi definido como caractere de escape
• o último % — qualquer continuação do texto

A cláusula:

ESCAPE '!'

diz ao SQL:

se antes de % ou de _ vier um !, trate o caractere seguinte como texto comum.

Do mesmo jeito dá para buscar o sublinhado:

SELECT name
FROM products
WHERE name LIKE 'QA!_%' ESCAPE '!';

Esse padrão encontra as linhas que começam com o texto comum QA_.

Sem escape, o padrão:

'QA_%'

significaria:

QA, depois um caractere qualquer, depois qualquer continuação.

E poderia trazer linhas a mais, como:

QA-набор
QA1набор
QA набор

QUERY: No estêncil do LIKE, os caracteres % e _ não são tinta, são furos. Se você quer encontrar o furo em si no desenho, avise antes ao arquivo que ali está um sinal comum.

O preço de um % inicial

Numa tabela pequena, a diferença quase não aparece. Mas, em milhões de linhas, padrões LIKE diferentes podem se comportar de formas bem distintas.

Compare duas condições:

WHERE name LIKE 'Кофе%'

e

WHERE name LIKE '%кофе'

A primeira condição conhece o começo do texto. O banco consegue raciocinar mais ou menos como você faria com um dicionário de papel:

Abra o dicionário nessa seção e procure ali por perto.

Essa busca por prefixo às vezes pode ser acelerada por um índice.

Já a segunda condição começa com %:

'%кофе'

Isso quer dizer:

antes dessa palavra pode vir qualquer coisa.

A correspondência pode estar em qualquer ponto do texto. Um índice comum na coluna name já não ajuda tanto, porque o banco não sabe por onde começar a procurar.

Por isso, consultas do tipo:

WHERE name LIKE '%кофе%'

costumam levar à leitura de um número enorme de linhas: o banco precisa verificar cada nome.

Uma ideia simples:

No PostgreSQL existem detalhes técnicos: para acelerar um LIKE por prefixo, às vezes é preciso um índice com text_pattern_ops ou um locale adequado, e para buscar no meio do texto usam-se os índices de trigramas pg_trgm.

Mas, nesta etapa, o importante é entender a ideia em si:

Se o padrão começa com caracteres comuns, fica mais fácil para o banco procurar. Se o padrão começa com %, muitas vezes o banco precisa reler uma parte bem maior da tabela.

A propriedade de uma condição "saber usar um índice" chama-se sargability. Vamos ver isso em detalhe no módulo sobre otimização.

UNKNOWN nas condições de lista e a armadilha do NOT IN

Na aula anterior você já viu que o NULL produz um terceiro resultado lógico:

UNKNOWN

Isso também vale para o IN.

Enquanto a lista é escrita à mão e só tem valores comuns, tudo é simples:

WHERE category IN ('Книги', 'Игрушки')

Isso é parecido com:

WHERE category = 'Книги'
   OR category = 'Игрушки'

Já o NOT IN funciona como uma sequência de condições ligadas por AND.

Por exemplo:

WHERE category NOT IN ('Книги', 'Игрушки')

é parecido com:

WHERE category <> 'Книги'
  AND category <> 'Игрушки'

E é aqui que aparece a armadilha.

Veja:

SELECT 'нашлось'
WHERE 1 NOT IN (2, 3);

Essa consulta devolve uma linha, porque:

1 <> 2 AND 1 <> 3

é:

TRUE AND TRUE

Resultado: TRUE.

E agora:

SELECT 'нашлось'
WHERE 1 NOT IN (2, NULL);

Essa consulta não devolve linha nenhuma.

Por quê?

O NOT IN (2, NULL) se desdobra mais ou menos assim:

1 <> 2 AND 1 <> NULL

A primeira parte:

1 <> 2

dá TRUE.

A segunda parte:

1 <> NULL

dá UNKNOWN.

Resultado:

TRUE AND UNKNOWN

dá UNKNOWN.

E o WHERE só deixa passar o que é TRUE.

Por isso a linha não chega ao resultado.

A situação mais perigosa não é aquela em que a lista é escrita à mão. Ali o NULL está à vista.

O perigo aparece com as subconsultas:

WHERE product_id NOT IN (
    SELECT product_id
    FROM archived_products
)

Se a subconsulta devolver ao menos um NULL, o resultado pode ficar inesperadamente vazio.

Por ora, guarde o sinal de alerta:

NOT IN + um NULL possível = risco de resultado vazio.

Nos próximos módulos, quando aparecerem as subconsultas, você vai conhecer alternativas mais seguras com NOT EXISTS.

O que acontece quando o valor é NULL

BETWEEN, IN e LIKE também obedecem à lógica do NULL.

Se o valor está ausente, uma verificação comum não resulta em TRUE.

Por exemplo:

price BETWEEN 1000 AND 3000

Se price for NULL, o resultado é UNKNOWN.

category IN ('Книги', 'Игрушки')

Se category for NULL, o resultado é UNKNOWN.

name LIKE 'Кофе%'

Se name for NULL, o resultado é UNKNOWN.

E o WHERE só deixa passar o que é TRUE.

Por isso as linhas com NULL não passam sozinhas por esses filtros.

Se você precisa incluí-las, acrescente uma condição explícita:

WHERE price BETWEEN 1000 AND 3000
   OR price IS NULL

ou:

WHERE category IN ('Книги', 'Игрушки')
   OR category IS NULL

Não ache que BETWEEN, IN ou LIKE "entendem" os valores ausentes de algum jeito especial. Para o NULL, continuam sendo necessários o IS NULL e o IS NOT NULL.

Erros comuns

Erro 1. Esquecer que o BETWEEN inclui os limites

WHERE price BETWEEN 1000 AND 3000

Isso inclui tanto 1000 quanto 3000.

Se os limites não devem entrar, escreva:

WHERE price > 1000
  AND price < 3000

Erro 2. Usar BETWEEN com datas e cortar sem querer o último dia

Cuidado:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

Se created_at também guarda o horário, parte dos registros de 31 de janeiro pode ficar de fora.

Costuma ser mais seguro assim:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Erro 3. Escrever muitos OR em vez de IN

Dá para fazer assim:

WHERE category = 'Книги'
   OR category = 'Игрушки'
   OR category = 'Аксессуары'

Mas fica mais legível assim:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары')

Erro 4. Achar que o IN procura texto parecido

WHERE category IN ('Книги')

Não vai encontrar:

Электронные книги

porque o IN verifica a correspondência exata.

Para buscar texto parecido, use o LIKE:

WHERE category LIKE '%книги%'

Ou o ILIKE, se você quer uma busca que ignore maiúsculas e minúsculas no PostgreSQL:

WHERE category ILIKE '%книги%'

Erro 5. Confundir % e _

LIKE 'A%'

A, depois qualquer quantidade de caracteres.

LIKE 'A_'

A, depois exatamente um caractere.

Erro 6. Esquecer de escapar % e _

Se você precisa encontrar o sinal % comum, não escreva assim:

WHERE name LIKE '%50%%'

É melhor escapar de forma explícita:

WHERE name LIKE '%50!%%' ESCAPE '!'

Erro 7. Colocar % no começo do padrão e esperar que a busca seja rápida

WHERE name LIKE '%кофе%'

Uma busca dessas pode ser tranquila numa tabela pequena, mas numa tabela grande costuma sair caro.

QUERY: Intervalo, lista e estêncil — três jeitos rápidos de perguntar ao arquivo. O importante é lembrar onde o modelo ajuda e onde ele obriga o arquivo a reler tudo, do começo ao fim.