SQL EXISTS

Muitas perguntas do dia a dia da Loja Aurora são de "sim ou não": este cliente já comprou alguma vez? Este produto já foi vendido? Este funcionário atendeu algum pedido? Não importa quantas vezes, nem quando: só importa se existe pelo menos um. Para isso o SQL tem o EXISTS ("existe"): ele recebe uma subconsulta e responde verdadeiro se ela encontrar pelo menos uma linha.

Você vai aprender

Como o EXISTS funciona e por que ele quase sempre usa uma subconsulta correlacionada.
O NOT EXISTS, para achar quem não tem nada ligado.
Por que se escreve SELECT 1 dentro do EXISTS.
O EXISTS como coluna, com resposta 0 ou 1.
Por que o NOT EXISTS não cai na armadilha do NOT IN com NULL.
EXISTS × IN × JOIN, e os erros reais do SQLite.

Esta aula continua a de Subconsultas; leia aquela primeiro, principalmente a parte da subconsulta correlacionada. Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: abrir a gaveta e olhar

No escritório da loja, cada cliente tem uma gaveta com os pedidos dele. Para saber se a Rosângela já comprou, a Ana não precisa contar os pedidos: ela abre a gaveta e olha. Achou um papel? Pronto, a resposta é "sim", e ela fecha a gaveta sem ler o resto. Gaveta vazia? "Não".

O EXISTS faz exatamente isso, para cada cliente: abre a "gaveta" (a subconsulta), olha se tem alguma linha e responde sim ou não. Não importa o que está escrito nos papéis, só se eles existem.

1. O primeiro EXISTS: quem já comprou

SELECT c.id, c.nome
FROM clientes AS c
WHERE EXISTS (SELECT 1
              FROM pedidos AS pe
              WHERE pe.cliente_id = c.id);

FROM clientes AS c: A consulta de fora passa pelos clientes, um por um. O apelido c é importante: a subconsulta vai usá-lo.
WHERE EXISTS ( … ): "Fica com o cliente se a subconsulta encontrar pelo menos uma linha." Os parênteses são obrigatórios.
FROM pedidos AS pe: A gaveta: a tabela onde procurar.
WHERE pe.cliente_id = c.id: A ligação: só os pedidos deste cliente. Ela usa o c.id da consulta de fora, então a subconsulta é correlacionada e roda de novo para cada cliente.
SELECT 1: O que a subconsulta mostra não importa, porque o EXISTS só olha se veio alguma linha. Por costume, escreve-se SELECT 1 ("um", qualquer coisa) para deixar isso claro. SELECT * funciona igual.

São 26 clientes: todos menos os 4 que nunca pediram. Siga dois deles:

José Ribamar (id 1): a subconsulta procura pedidos com cliente_id = 1. Acha (ele tem pedidos). EXISTS é verdadeiro: o José entra.
Rosângela (id 30): a subconsulta procura pedidos com cliente_id = 30. Não acha nenhum. EXISTS é falso: a Rosângela fica de fora.

2. NOT EXISTS: quem não tem nada

Ponha um NOT (aula NOT) na frente e a pergunta vira o contrário: os clientes cuja gaveta está vazia.

SELECT c.id, c.nome
FROM clientes AS c
WHERE NOT EXISTS (SELECT 1
                  FROM pedidos AS pe
                  WHERE pe.cliente_id = c.id);

Os clientes 27 a 30. O mesmo molde serve para qualquer "nunca": os produtos que nunca foram vendidos, procurando na gaveta itens_pedido:

SELECT p.nome
FROM produtos AS p
WHERE NOT EXISTS (SELECT 1
                  FROM itens_pedido AS i
                  WHERE i.produto_id = p.id);

Missão: quais funcionários nunca atenderam um pedido? A tabela pedidos guarda quem atendeu na coluna funcionario_id. Acrescente um WHERE NOT EXISTS (…) à consulta abaixo.

SELECT f.nome, f.cargo
FROM funcionarios AS f;

SELECT f.nome, f.cargo
FROM funcionarios AS f
WHERE NOT EXISTS (SELECT 1
                  FROM pedidos AS pe
                  WHERE pe.funcionario_id = f.id);

Faz sentido: a Marta é a gerente e o Davi é o estoquista. Nenhum dos dois fica no balcão.

3. Mais condições dentro da gaveta

A subconsulta do EXISTS pode ter quantas condições você quiser. Os clientes que têm algum pedido ainda não entregue:

SELECT c.nome, c.cidade
FROM clientes AS c
WHERE EXISTS (SELECT 1
              FROM pedidos AS pe
              WHERE pe.cliente_id = c.id
                AND pe.entregue_em IS NULL);

A primeira condição liga o pedido ao cliente; a segunda (aula IS NULL) exige que ele esteja pendente. São os 5 clientes dos pedidos 86 a 90. A subconsulta pode ter até JOIN dentro. Os clientes que já compraram algum produto de Eletrônicos (categoria 2):

SELECT c.nome
FROM clientes AS c
WHERE EXISTS (SELECT 1
              FROM pedidos AS pe
              INNER JOIN itens_pedido AS i ON i.pedido_id = pe.id
              WHERE pe.cliente_id = c.id
                AND i.produto_id IN (SELECT id FROM produtos WHERE categoria_id = 2))
ORDER BY c.nome;

Dezoito clientes. A Ana não quer saber quantos eletrônicos cada um comprou, só se comprou: pergunta de EXISTS.

4. EXISTS como coluna: 0 ou 1

O EXISTS não serve só no WHERE. Ele é uma expressão que vale verdadeiro ou falso, e o SQLite, que não tem um tipo especial para isso, escreve 1 para verdadeiro e 0 para falso. Uma pergunta solta, sem tabela de fora: "tem algum pedido pendente?"

SELECT EXISTS (SELECT 1 FROM pedidos WHERE entregue_em IS NULL) AS tem_pendente;

1: sim. E "algum frete passou de 100?":

SELECT EXISTS (SELECT 1 FROM pedidos WHERE frete > 100) AS tem_frete_caro;

0: não. Como coluna, ao lado de cada cliente de Fortaleza:

SELECT c.nome,
       EXISTS (SELECT 1 FROM pedidos AS pe WHERE pe.cliente_id = c.id) AS ja_comprou
FROM clientes AS c
WHERE c.cidade = 'Fortaleza';

Todo mundo de Fortaleza já comprou, menos a Rosângela. Quer escrever "sim" e "não" no lugar de 1 e 0? Isso é trabalho do CASE, duas aulas adiante.

O que tem dentro não importa mesmo

Prova: uma subconsulta que devolve uma linha com NULL ainda é uma linha, e o EXISTS diz que existe:

SELECT EXISTS (SELECT NULL) AS resposta;

1. O EXISTS não olha os valores, só conta se veio pelo menos uma linha.

5. NOT EXISTS não cai na armadilha do NULL

Na aula de Subconsultas, a Ana procurou os clientes de estados onde a loja não tem fornecedor com NOT IN e recebeu zero linhas, porque a lista tinha um NULL (os fornecedores estrangeiros não têm estado). Com NOT EXISTS:

SELECT c.nome, c.estado
FROM clientes AS c
WHERE NOT EXISTS (SELECT 1
                  FROM fornecedores AS fo
                  WHERE fo.estado = c.estado);

A Juliana e o Bruno, de Pernambuco, de primeira. Por quê? Para a Juliana, a subconsulta procura fornecedores com estado = 'PE'. Os de estado NULL não passam nessa comparação, então simplesmente não aparecem; não há nenhum, a gaveta está vazia, e NOT EXISTS é verdadeiro. O NULL não "contamina" a resposta como no NOT IN.

Regra prática

Para "quem não tem…", prefira NOT EXISTS a NOT IN. Os dois dão o mesmo resultado quando não há NULL, mas só o NOT EXISTS continua certo quando aparece um.

6. Formas diferentes: EXISTS × IN × JOIN

"Os clientes com pedido pendente", da seção 3, de mais dois jeitos. Com IN:

SELECT nome
FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE entregue_em IS NULL);

Com JOIN (o DISTINCT tira o cliente repetido, se ele tiver dois pedidos pendentes):

SELECT DISTINCT c.nome
FROM clientes AS c
INNER JOIN pedidos AS pe ON pe.cliente_id = c.id
WHERE pe.entregue_em IS NULL;

Os mesmos 5 clientes nas três formas.

Forma	Bom para	Cuidado
`EXISTS (SELECT 1 … WHERE ligação)`	"Tem pelo menos um?", com várias condições na ligação	Não esqueça a ligação com a consulta de fora
`NOT EXISTS (…)`	"Não tem nenhum?", mesmo com `NULL` por perto	O mesmo: a ligação
`IN (SELECT …)`	Listas simples de uma coluna; fácil de ler	—
`NOT IN (SELECT …)`	Listas sem `NULL`	Um `NULL` na lista zera o resultado
`JOIN`	Quando você também quer mostrar colunas da outra tabela	Repete linhas; pode precisar de `DISTINCT`

Quando dá errado

Esqueceu a ligação: todo mundo ou ninguém

A Ana queria os clientes com pedido pendente, mas esqueceu de ligar o pedido ao cliente:

SELECT COUNT(*) AS clientes
FROM clientes
WHERE EXISTS (SELECT 1 FROM pedidos WHERE entregue_em IS NULL);

O que acontece: 30, todos os clientes, sem erro. Sem a ligação, a subconsulta pergunta "existe algum pedido pendente na loja?" e a resposta é sempre a mesma, sim, para qualquer cliente. Um EXISTS sem ligação deixa passar todas as linhas ou nenhuma.

Como consertar: dentro da subconsulta, ligue à consulta de fora: WHERE pe.cliente_id = c.id AND pe.entregue_em IS NULL.

Ligação sem apelidos: o id errado

SELECT nome
FROM clientes
WHERE EXISTS (SELECT cliente_id FROM pedidos WHERE cliente_id = id);

O que acontece: saem os 30 clientes, inclusive os 4 que nunca pediram. A Ana achou que id era o do cliente, mas, dentro da subconsulta, o banco procura o nome primeiro na tabela de dentro, e pedidos também tem uma coluna id. A condição virou "o cliente_id do pedido é igual ao id do próprio pedido", que não tem nada a ver com o cliente de fora. A subconsulta deixou de ser correlacionada.

Como consertar: use apelidos e escreva sempre de qual tabela é cada coluna: pe.cliente_id = c.id.

Apelido que não existe

SELECT nome
FROM clientes
WHERE EXISTS (SELECT 1 FROM pedidos AS pe WHERE pe.cliente_id = c.id);

A mensagem: no such column: c.id, "coluna c.id não existe". A subconsulta usa o apelido c, mas a consulta de fora não deu esse apelido à tabela clientes.

Como consertar: FROM clientes AS c.

EXISTS sem subconsulta

SELECT nome
FROM clientes
WHERE EXISTS pedidos;

A mensagem: near "pedidos": syntax error. O EXISTS não aceita o nome de uma tabela: ele precisa de uma subconsulta entre parênteses.

Como consertar: WHERE EXISTS (SELECT 1 FROM pedidos AS pe WHERE pe.cliente_id = c.id).

Nos outros bancos

EXISTS e NOT EXISTS são SQL padrão e se escrevem igual no MySQL, no PostgreSQL e no SQL Server. O manual do PostgreSQL diz o mesmo que esta aula: o resultado só depende de a subconsulta devolver alguma linha, não do conteúdo dela, e o costume é escrever EXISTS(SELECT 1 WHERE …). Uma diferença de aparência: o PostgreSQL tem um tipo próprio para verdadeiro/falso (boolean), então lá o resultado de SELECT EXISTS (…) é um valor desse tipo (verdadeiro ou falso), e não o número 1 ou 0.

Referência rápida

Escrita	O que faz
`WHERE EXISTS (SELECT 1 FROM t WHERE t.x = fora.y)`	Fica com a linha de fora se houver pelo menos uma linha ligada
`WHERE NOT EXISTS (…)`	Fica com a linha de fora se não houver nenhuma linha ligada
`SELECT EXISTS (…) AS x`	Uma coluna com 1 (existe) ou 0 (não existe)
`SELECT 1` dentro do `EXISTS`	Costume: o conteúdo não importa, só se há linhas

Mensagem ou sintoma	Quer dizer
Vieram todas as linhas (ou nenhuma)	Faltou ligar a subconsulta à consulta de fora
`no such column: c.id`	A consulta de fora não tem o apelido `c`
`near "pedidos": syntax error`	`EXISTS` precisa de uma subconsulta entre parênteses

Exercícios

1. Nenhum pedido

Complete para mostrar os clientes que nunca fizeram pedido:

SELECT c.nome FROM clientes AS c WHERE (SELECT 1 FROM pedidos AS pe WHERE pe.cliente_id = c.id);

2. A ligação

Complete a ligação para achar os produtos que já foram vendidos:

SELECT p.nome FROM produtos AS p WHERE EXISTS (SELECT 1 FROM itens_pedido AS i WHERE i.produto_id = );

3. Por que se escreve SELECT 1 dentro do EXISTS?

Porque o EXISTS só aceita o número 1 Porque o EXISTS só verifica se há linhas; o que a subconsulta mostra não importa Para limitar a subconsulta a uma linha Porque SELECT * dá erro dentro do EXISTS

4. A Ana escreveu WHERE EXISTS (SELECT 1 FROM pedidos WHERE frete = 0), sem ligação com o cliente. O que acontece?

Saem só os clientes com frete grátis Dá erro de sintaxe Saem os clientes sem frete grátis Saem todos os clientes, porque existe algum pedido com frete grátis na loja

5. Para achar "quem não tem nenhum…" numa lista que pode ter NULL, o mais seguro é:

NOT EXISTS NOT IN IN EXISTS

6. Quanto vale SELECT EXISTS (SELECT 1 FROM pedidos WHERE frete > 100) na Loja Aurora?

O número de pedidos com frete acima de 100 NULL 0, porque nenhum frete passa de 100 1

Missão final: quem compra eletrônicos

Missão: a Ana vai mandar um folheto de eletrônicos e quer o nome dos clientes que já compraram algum produto de Eletrônicos (categoria_id = 2). A consulta abaixo pega todo mundo que já comprou qualquer coisa (26 clientes). Mude a subconsulta: ligue também os itens do pedido (itens_pedido) e os produtos, e exija a categoria 2.

SELECT c.nome
FROM clientes AS c
WHERE EXISTS (SELECT 1
              FROM pedidos AS pe
              WHERE pe.cliente_id = c.id);

SELECT c.nome
FROM clientes AS c
WHERE EXISTS (SELECT 1
              FROM pedidos AS pe
              INNER JOIN itens_pedido AS i ON i.pedido_id = pe.id
              INNER JOIN produtos AS p ON p.id = i.produto_id
              WHERE pe.cliente_id = c.id
                AND p.categoria_id = 2);

Livre: esta consulta lista os funcionários que chefiam alguém (um EXISTS na própria tabela, como no Self Join). Troque por NOT EXISTS e veja quem não chefia ninguém.

SELECT f.nome
FROM funcionarios AS f
WHERE EXISTS (SELECT 1 FROM funcionarios AS s WHERE s.chefe_id = f.id);

Resumo

EXISTS (subconsulta) é verdadeiro se a subconsulta devolver pelo menos uma linha; NOT EXISTS, se não devolver nenhuma.
Quase sempre a subconsulta é correlacionada: ela se liga à linha de fora (pe.cliente_id = c.id). Sem a ligação, passam todas as linhas ou nenhuma.
O conteúdo da subconsulta não importa: o costume é SELECT 1.
No SELECT, o EXISTS vira uma coluna com 1 ou 0.
Para "quem não tem…", prefira NOT EXISTS: ele não cai na armadilha do NULL do NOT IN.
Use apelidos e escreva a tabela antes de cada coluna; um id solto pode ser o da tabela errada.