SQL IN

A Loja Aurora vai fazer uma entrega especial nas cidades de Recife, Picos e Oeiras. Com o que você já sabe, a Ana escreveria cidade = 'Recife' OR cidade = 'Picos' OR cidade = 'Oeiras'. Funciona, mas e se fossem dez cidades? O IN ("em", em inglês: "a cidade está em esta lista?") faz a mesma pergunta de um jeito curto: cidade IN ('Recife', 'Picos', 'Oeiras'). Nesta aula você vai usá-lo, conhecer o seu contrário, o NOT IN, e ver de perto a armadilha mais famosa dele: um NULL dentro da lista.

Os exemplos usam o banco da Loja Aurora, e cada quadro "Resultado" vem do SQLite de verdade, rodando no seu navegador. Antes desta aula, vale ter visto OR e NOT.

A ideia: a lista de convidados

Na porta de uma festa, o segurança tem uma lista de nomes. Para cada pessoa que chega, ele faz uma pergunta só: "o nome está na lista?". Ele não precisa saber quantos nomes há, nem a ordem deles. O IN é esse segurança: para cada linha da tabela, ele pergunta se o valor da coluna está na lista.

1. O primeiro IN

SELECT nome, cidade
FROM clientes
WHERE cidade IN ('Recife', 'Picos', 'Oeiras');

cidade

A coluna que vai ser comparada.

IN

"Em": a pergunta é "o valor está nesta lista?".

( … )

A lista vai entre parênteses.

'Recife', 'Picos', 'Oeiras'

Os valores, separados por vírgula. São textos, então cada um vai entre aspas simples.

Quatro clientes: dois de Recife, uma de Picos e um de Oeiras. Compare com a versão com OR, que dá exatamente as mesmas linhas:

SELECT nome, cidade
FROM clientes
WHERE cidade = 'Recife' OR cidade = 'Picos' OR cidade = 'Oeiras';

Uma coisa que o IN não faz: ordenar. Os clientes vieram na ordem da tabela, não na ordem da lista. Para ordenar, use ORDER BY.

SELECT nome, cidade
FROM clientes
WHERE cidade = 'Recife';

SELECT nome, cidade
FROM clientes
WHERE cidade IN ('Recife', 'Picos', 'Oeiras');

2. IN com números

A lista pode ter números, sem aspas. Os produtos das categorias Livros (4) e Alimentos (7):

SELECT nome, categoria_id, preco
FROM produtos
WHERE categoria_id IN (4, 7);

Um uso muito comum: buscar linhas pelo id, quando você já sabe quais quer. Os dois produtos descontinuados e um dos que nunca foram vendidos:

SELECT id, nome
FROM produtos
WHERE id IN (12, 27, 43);

Algumas regras da lista, que valem para textos e números:

• Um valor só também serve: estado IN ('PE') é o mesmo que estado = 'PE'.
• Valores repetidos não atrapalham: estado IN ('PE', 'PE', 'PE') traz os mesmos 2 clientes de Pernambuco, sem repetir ninguém.
• A ordem dos valores não importa.
• Cada valor é comparado com =, então maiúsculas contam: cidade IN ('teresina', 'recife') não acha ninguém, porque está guardado "Teresina" e "Recife".
• O IN não conhece curingas: estado IN ('P%') procura o texto "P%" de verdade e não acha nada. Para padrões, use LIKE.

3. NOT IN: "nenhum destes"

Com NOT antes do IN, a pergunta vira "o valor não está na lista?". Os clientes que não são do Piauí, do Ceará nem de São Paulo:

SELECT nome, estado
FROM clientes
WHERE estado NOT IN ('PI', 'CE', 'SP');

Sobraram os 2 clientes de Pernambuco. O NOT IN é o mesmo que vários <> unidos por AND: estado <> 'PI' AND estado <> 'CE' AND estado <> 'SP'. Repare: com o NOT, o OR virou AND, como na troca de De Morgan da aula NOT. Também dá para escrever NOT estado IN ('PI', 'CE', 'SP'), com o NOT na frente; o resultado é o mesmo.

SELECT nome, categoria_id
FROM produtos
WHERE categoria_id IN (1, 2, 3);

SELECT nome, categoria_id
FROM produtos
WHERE categoria_id NOT IN (1, 2, 3);

4. A armadilha do NULL na lista

Este é o ponto mais importante da aula. A Ana está montando a lista de estados para excluir e, por descuido (ou porque a lista veio de uma planilha com uma célula vazia), entra um NULL no meio:

SELECT nome, estado
FROM clientes
WHERE estado NOT IN ('PI', 'CE', 'SP', NULL);

Zero linhas. Nenhum erro, nenhum aviso: os 2 clientes de Pernambuco simplesmente sumiram. E não importa quais estados estão na lista: com um NULL lá dentro, esse NOT IN nunca devolve linha nenhuma.

Por quê?

Lembre-se de que NOT IN é uma fila de <> unidos por AND. Para a Juliana, de Pernambuco, o SQLite faz estas contas:

Um único "não sei" no AND deixa o resultado inteiro em "não sei", e o WHERE só deixa passar o que é verdadeiro. A documentação do SQLite traz essa regra numa tabela: se a lista contém NULL e o valor não foi encontrado nela, o resultado do IN e do NOT IN é NULL. Veja as contas direto:

SELECT 'PE' IN ('PI', 'CE'),
       'PE' NOT IN ('PI', 'CE'),
       'PE' IN ('PI', NULL),
       'PE' NOT IN ('PI', NULL),
       'PI' IN ('PI', NULL),
       'PI' NOT IN ('PI', NULL);

Repare que, no IN sem NOT, o estrago é menor: estado IN ('PI', NULL) acha normalmente os 14 clientes do Piauí, porque, quando o valor é encontrado, a resposta é "sim" de qualquer jeito. O problema sério é o NOT IN: lá, o NULL transforma todo "não está na lista" em "não sei".

E o NULL na coluna (não na lista)?

Outra situação, parecida: a lista está limpa, mas a coluna tem NULL. Os fornecedores que não são do Piauí:

SELECT nome, estado
FROM fornecedores
WHERE estado NOT IN ('PI');

Vieram 2, mas os fornecedores de Portugal e da Argentina, que têm estado NULL, também "não são do Piauí" e ficaram de fora. É a mesma armadilha da aula NOT: NULL NOT IN (…) é "não sei". Para incluí-los, acrescente OR estado IS NULL (aula IS NULL).

5. Prévia: IN com uma consulta dentro

A lista do IN não precisa ser escrita à mão: ela pode ser o resultado de outro SELECT, entre parênteses. Isso se chama subconsulta e tem aula própria (Subconsultas); aqui vai só uma prévia, porque é onde a armadilha do NULL mais aparece na vida real.

Os produtos que nunca foram vendidos são os que não aparecem em nenhum item de pedido:

SELECT id, nome
FROM produtos
WHERE id NOT IN (SELECT produto_id FROM itens_pedido);

Os produtos 43, 44 e 45. O SELECT de dentro devolve a lista de todos os produto_id vendidos, e o NOT IN fica com os que não estão nela. Funcionou porque produto_id nunca é NULL.

Agora a Ana quer os funcionários que não chefiam ninguém: os que não aparecem na coluna chefe_id de nenhum colega.

SELECT id, nome
FROM funcionarios
WHERE id NOT IN (SELECT chefe_id FROM funcionarios);

Zero linhas! Mas a loja tem 6 funcionários que não chefiam ninguém. O que aconteceu: a gerente, Marta Lustosa, não tem chefe, e o chefe_id dela é NULL. A lista que a subconsulta devolveu tem um NULL no meio, e caímos exatamente na armadilha da seção 4. O conserto é tirar os vazios da lista:

SELECT id, nome
FROM funcionarios
WHERE id NOT IN (SELECT chefe_id FROM funcionarios WHERE chefe_id IS NOT NULL);

Agora vieram os 6: Caio, Renata, Pedro, Igor, Sofia e Davi. Esse é um dos erros mais comuns em consultas de verdade, justamente porque não dá mensagem: a consulta "roda", só que vazia. Outro jeito de escrever, que não sofre com o NULL, é o NOT EXISTS (aula EXISTS).

6. Formas diferentes de escrever

Quando dá errado

SELECT nome FROM clientes WHERE estado IN 'PI', 'SP';

SELECT nome FROM clientes WHERE estado IN ('PI' 'SP');

SELECT nome FROM clientes WHERE estado IN (PI, SP);

SELECT nome FROM clientes WHERE estado IN ();

Referência rápida

Exercícios

Missão final: o NULL que sumiu com tudo

SELECT nome, cidade, estado
FROM clientes
WHERE estado NOT IN ('PI', 'CE', 'SP', NULL);

SELECT nome, cidade, estado
FROM clientes
WHERE estado NOT IN ('PI', 'CE', 'SP');

Resumo

• IN ("em") pergunta se o valor está numa lista: cidade IN ('Recife', 'Picos', 'Oeiras'). É um atalho para vários = com OR.
• A lista vai entre parênteses, com vírgulas; textos entre aspas simples, números sem aspas. Ordem e repetições não importam; maiúsculas importam; curingas não existem.
• NOT IN é "nenhum destes": vários <> com AND.
• Com NULL na lista, NOT IN nunca é verdadeiro: devolve 0 linhas, sem erro. Com NULL na coluna, a linha fica de fora do IN e do NOT IN.
• A lista pode vir de uma subconsulta. Se a coluna de dentro pode ter NULL, filtre com WHERE … IS NOT NULL antes de usar NOT IN, ou use NOT EXISTS.
• O SQLite aceita a lista vazia IN (); os outros bancos, não.