Consultas SQL, NULL e agregação

A Loja de Bilhetes quer mostrar uma linha por sessão, incluindo as que ainda não venderam bilhetes. Antes do SQL, fixa o significado da linha e o resultado esperado quando não há correspondências. Uma consulta que lista bilhetes vendidos responde a outra pergunta, mesmo que use as mesmas tabelas.

Ordem lógica e repetidos

Uma ordem útil para raciocinar é FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. É uma ordem lógica, não a sequência física do plano de execução. WHERE filtra linhas antes da agregação; HAVING filtra grupos depois dela.

SELECT sessao_id, COUNT(*) AS vendidos
FROM bilhetes
WHERE estado = 'pago'
GROUP BY sessao_id
HAVING COUNT(*) >= 2
ORDER BY vendidos DESC, sessao_id;

O alias vendidos pode ser usado no ORDER BY em PostgreSQL. Não o uses no WHERE, onde a projeção ainda não foi definida. Uma coluna selecionada que não é agregada tem de ser compatível com o agrupamento; no exemplo, sessao_id é a chave do grupo.

SQL conserva repetidos por defeito. Uma junção pode repetir a mesma sessão uma vez por bilhete. DISTINCT retira linhas iguais da projeção, mas não corrige uma condição de junção em falta. UNION elimina repetidos entre resultados; UNION ALL conserva-os e evita esse trabalho.

NULL usa três valores lógicos

NULL representa informação ausente, não zero nem texto vazio. Comparações como preco = NULL dão desconhecido, não verdadeiro. Usa IS NULL e IS NOT NULL. O WHERE mantém apenas condições verdadeiras, por isso descarta falso e desconhecido.

COUNT(*) conta linhas; COUNT(coluna) conta valores não nulos. SUM, AVG, MIN e MAX ignoram valores nulos. SUM sem valores dá NULL; usa COALESCE(SUM(preco),0) quando a ausência deve significar zero. COALESCE é uma escolha do significado do resultado: ausência de valores e soma conhecida igual a zero são situações distintas.

LEFT JOIN e o lugar do filtro

Para mostrar sessões sem bilhetes pagos:

SELECT s.id, COUNT(b.id) AS pagos
FROM sessoes s
LEFT JOIN bilhetes b
  ON b.sessao_id = s.id AND b.estado = 'pago'
GROUP BY s.id
ORDER BY s.id;

O filtro no ON determina que bilhetes correspondem. Uma sessão sem correspondência ganha uma linha com as colunas de b a NULL. COUNT(b.id) dá zero nessa linha; COUNT(*) daria um. Mover b.estado = 'pago' para o WHERE elimina essa linha e deixa de conservar as sessões vazias.

Não existe uma regra universal «todo o filtro da direita vai no ON». Depende da pergunta. Se queres excluir sessões sem bilhetes pagos, um INNER JOIN exprime esse objetivo diretamente.

EXISTS, NOT IN e «todos»

EXISTS testa se a subconsulta produz alguma linha. Não precisa de devolver uma coluna útil, por isso é habitual escrever SELECT 1:

SELECT s.id
FROM sessoes s
WHERE NOT EXISTS (
  SELECT 1 FROM bilhetes b
  WHERE b.sessao_id = s.id AND b.estado = 'pago'
);

Este SQL procura sessões sem bilhetes pagos. s.id NOT IN (SELECT sessao_id FROM bilhetes) tem uma armadilha se a subconsulta puder devolver NULL. Por exemplo, 2 NOT IN (1,NULL) é desconhecido e o WHERE rejeita a linha. NOT EXISTS com a correlação adequada evita esse efeito; se sessao_id for NOT NULL, esse caso específico deixa de existir.

Para procurar utilizadores que compraram em todas as sessões de um evento, lê «não existe uma sessão para a qual não exista uma compra»:

SELECT u.id
FROM utilizadores u
WHERE NOT EXISTS (
  SELECT 1 FROM sessoes s
  WHERE s.evento_id = 3
    AND NOT EXISTS (
      SELECT 1 FROM bilhetes b
      WHERE b.sessao_id = s.id AND b.utilizador_id = u.id
        AND b.estado = 'pago'
    )
);

Se o evento não tiver sessões, todos os utilizadores passam: é a verdade por vacuidade. Se a pergunta exigir pelo menos uma sessão, acrescenta um EXISTS que verifique essa condição. A cláusula «todos» não implica «existe».

Agregar sem esconder o detalhe

Uma função de janela calcula sobre um grupo de linhas e mantém cada linha:

SELECT id, sessao_id, preco,
       SUM(preco) OVER (PARTITION BY sessao_id) AS total_sessao,
       ROW_NUMBER() OVER (PARTITION BY sessao_id ORDER BY id) AS numero
FROM vendas;

Uma sessão com dois bilhetes de 10 euros continua a ter duas linhas, ambas com total_sessao=20. Com GROUP BY sessao_id, teríamos uma linha por sessão. Uma CTE, WITH nome AS (...), dá nome a um resultado intermédio e ajuda a decompor a pergunta; não é uma promessa de materialização nem de melhor desempenho.

Para prever o resultado de agregações, operações de conjuntos e funções de janela, pergunta «que linhas existem antes e depois de cada operação?»: pausa antes de cada resultado, escreve as linhas que esperas e só depois confirma.

CMU Database Group, Modern SQL, 2024Carregar vídeo do YouTube

Abrir no YouTube

CREATE TABLE sessoes(id INTEGER PRIMARY KEY);
CREATE TABLE bilhetes(id INTEGER PRIMARY KEY,sessao_id INTEGER,estado TEXT);
INSERT INTO sessoes VALUES(1),(2),(3);
INSERT INTO bilhetes VALUES(10,1,'pago'),(11,1,'cancelado'),(12,3,'cancelado');
SELECT s.id,COUNT(b.id) AS pagos
FROM sessoes s LEFT JOIN bilhetes b
ON b.sessao_id=s.id AND b.estado='pago'
GROUP BY s.id ORDER BY s.id;
SELECT (SELECT COUNT(*) FROM (SELECT 2 WHERE 2 NOT IN (1,NULL))) AS not_in,
       (SELECT COUNT(*) FROM (SELECT 2 WHERE NOT EXISTS
         (SELECT 1 FROM bilhetes WHERE sessao_id=2))) AS not_exists;

Este bloco corre em PostgreSQL, com dados que incluem sessões sem bilhetes e um NULL que distingue NOT IN de NOT EXISTS. Cada consulta tem a sua tabela de resultados. Uma execução usa uma só ligação, por isso as consultas seguidas não representam transações concorrentes.

Confirmar a resposta antes de otimizar

Constrói dados mínimos com zero, uma e várias correspondências; inclui repetidos e NULL se forem permitidos. Calcula à mão as linhas esperadas e só depois consulta o plano e os índices, porque um índice torna mais rápida uma resposta errada sem a tornar certa.

Relação com a álgebra relacional

Na álgebra relacional clássica, seleção σ filtra tuplos e projeção π escolhe atributos; junção combina relações por uma condição. União, interseção e diferença exigem esquemas compatíveis. O renomeamento ρ permite distinguir duas utilizações da mesma relação.

SELECT id FROM sessoes WHERE evento_id=3 corresponde a projetar id depois de selecionar evento_id=3, se abstrairmos NULL e os repetidos de SQL. A projeção clássica é um conjunto e elimina repetidos; SQL precisa de DISTINCT para essa semântica. Um produto cartesiano sem condição cria todas as combinações, que um filtro pode reduzir. No plano físico, o motor pode executar uma junção de outra forma sem alterar o resultado lógico.

O mesmo conjunto de dados do início ao fim

O bloco PostgreSQL anterior usa três sessões. S1 tem um bilhete pago e outro cancelado; S2 não tem bilhetes; S3 só tem um cancelado. As tabelas auxiliares omitem deliberadamente as FKs para isolar o comportamento das junções e de NULL, sem representar todo o esquema da loja.

S3 distingue “tem bilhetes” de “tem correspondências pagas”. Ao filtrar no ON, o bilhete cancelado não corresponde e a junção conserva a sessão com colunas de bilhete a NULL. Ao filtrar no WHERE, a sua única linha é rejeitada, tal como a linha exterior de S2.

A expressão isolada 2 NOT IN (1,NULL) é desconhecida e rejeita a linha. O NOT EXISTS correlacionado encontra ausência de bilhetes em S2 e aceita-a. Este NULL pertence à lista da expressão de teste, não a um bilhete inserido pelo programa.

Duas relações podem multiplicar a mesma venda

S12 tem dois bilhetes pagos de 20 euros e três etiquetas: fado, Porto e noite. Uma junção dos bilhetes com as etiquetas produz uma linha para cada par bilhete-etiqueta:

Somar o preço depois desta junção dá 120 euros, embora só tenham sido vendidos 40. COUNT(b.id) também conta seis ocorrências, não dois bilhetes. As condições de junção podem estar todas corretas: a multiplicação resulta de combinar duas relações de um para muitos.

COUNT(DISTINCT b.id) recupera a quantidade de bilhetes neste caso. Mas SUM(DISTINCT b.preco) dá 20 e perde a segunda compra legítima ao mesmo preço. A identidade do bilhete e o valor do preço são coisas diferentes.

Para obter uma linha por sessão com quantidade paga, receita e número de etiquetas, agrega cada relação antes de as juntar. Cada resultado intermédio fica com no máximo uma linha por sessão; juntar esses resultados já não repete as vendas.

CREATE TABLE sessoes(id integer PRIMARY KEY);
CREATE TABLE bilhetes(id integer PRIMARY KEY, sessao_id integer REFERENCES sessoes(id), preco numeric(8,2) NOT NULL, estado text NOT NULL);
CREATE TABLE etiquetas(sessao_id integer REFERENCES sessoes(id), nome text NOT NULL, PRIMARY KEY(sessao_id,nome));
INSERT INTO sessoes VALUES (12),(13),(14);
INSERT INTO bilhetes VALUES (1,12,20,'pago'),(2,12,20,'pago'),(3,14,8,'pago');
INSERT INTO etiquetas VALUES (12,'fado'),(12,'porto'),(12,'noite');
SELECT s.id, COUNT(b.id) AS pagos, SUM(b.preco) AS receita
FROM sessoes s LEFT JOIN bilhetes b ON b.sessao_id=s.id AND b.estado='pago'
LEFT JOIN etiquetas e ON e.sessao_id=s.id
GROUP BY s.id ORDER BY s.id;
WITH pagos AS (
  SELECT sessao_id, COUNT(*) AS quantidade, SUM(preco) AS receita
  FROM bilhetes WHERE estado='pago' GROUP BY sessao_id
), tags AS (
  SELECT sessao_id, COUNT(*) AS quantidade FROM etiquetas GROUP BY sessao_id
)
SELECT s.id, COALESCE(p.quantidade,0) AS pagos,
       COALESCE(p.receita,0) AS receita, COALESCE(t.quantidade,0) AS etiquetas
FROM sessoes s LEFT JOIN pagos p ON p.sessao_id=s.id
LEFT JOIN tags t ON t.sessao_id=s.id ORDER BY s.id;

A primeira consulta mostra a multiplicação. A segunda devolve S12 com dois pagos, receita 40 e três etiquetas; S13 com zeros; e S14 com um pago de 8 euros, mesmo sem etiquetas. COALESCE atribui zero à ausência de grupo neste relatório. Se só queres testar se existe uma etiqueta, EXISTS pode filtrar sem acrescentar linhas à relação de vendas.