# Consultas SQL, NULL e agregação

Junções, quantificação, grupos e consultas analíticas, com resultados calculados sobre dados pequenos.

Página: https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/

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.

| Expressão | Resultado |
| --- | --- |
| TRUE AND NULL | desconhecido |
| FALSE AND NULL | falso |
| TRUE OR NULL | verdadeiro |
| FALSE OR NULL | desconhecido |
| NOT NULL | 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.

[Vídeo: CMU Database Group, Modern SQL, 2024](https://www.youtube.com/watch?v=MzigBKf84aY)

[Abrir no YouTube](https://www.youtube.com/watch?v=MzigBKf84aY)

**PostgreSQL**



**main.sql**

```sql
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](https://resumos.rgo.pt/cadeiras/lbaw/sql-indices/), 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.

| Operação | S1 | S2 | S3 |
| --- | --- | --- | --- |
| Filtro pago no ON e COUNT(b.id) | 1 | 0 | 0 |
| Filtro pago no WHERE | 1 | Linha eliminada | Linha eliminada |

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:

| Bilhete | Preço | Etiqueta |
| --- | --- | --- |
| B1 | 20 | fado |
| B1 | 20 | Porto |
| B1 | 20 | noite |
| B2 | 20 | fado |
| B2 | 20 | Porto |
| B2 | 20 | noite |

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.

**PostgreSQL**



**main.sql**

```sql
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.

## Exercícios

O caso NULL

Que valor lógico tem 2 `NOT IN` (1,`NULL`)?

Pista

Expande como 2<>1 AND 2<>`NULL`.

Mais uma pista

TRUE AND desconhecido é desconhecido.

Solução

O resultado é desconhecido e o `WHERE` rejeita a linha. Uma subconsulta `NOT EXISTS` adequada evita esta armadilha.

*   **Desconhecido..** 2 difere de 1, mas a comparação com `NULL` é desconhecida.
*   **Verdadeiro..** A lista contém uma informação ausente.
*   **Falso por 2=1..** Essa comparação é falsa, não verdadeira.

Erros frequentes

Não trates `NULL` como um valor concreto diferente de 2.

[Voltar à explicação](https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/#exists-not-in-e-todos)

Quantificação sobre conjunto vazio

O duplo `NOT EXISTS` para «comprou em todas as sessões» aceita um evento sem sessões?

Pista

Procura uma sessão que possa ser contraexemplo.

Mais uma pista

O conjunto de sessões está vazio.

Solução

Aceita por vacuidade. Se o domínio exige pelo menos uma sessão, acrescenta um EXISTS que o afirme.

*   **Sim, salvo uma condição adicional de existência..** Não há nenhuma sessão que viole a condição.
*   **Não, todos significa pelo menos um..** A quantificação universal não exige existência.
*   **Só se o utilizador tiver um bilhete nulo..** A condição exterior já não encontra sessões.

Erros frequentes

Não introduzas uma pré-condição que o SQL não contém.

[Voltar à explicação](https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/#exists-not-in-e-todos)

Manter ou reduzir linhas

Duas vendas da mesma sessão valem 8 e 12. `SUM(preco)` OVER(PARTITION BY `sessao_id`) produz quantas linhas?

Pista

Uma janela mantém o detalhe de cada linha.

Mais uma pista

Cada linha recebe o mesmo total 20.

Solução

Produz duas linhas, com total 20 em cada uma. GROUP BY produziria uma linha por sessão.

Erros frequentes

Não confundas agregação de janela com agregação que reduz grupos.

[Voltar à explicação](https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/#agregar-sem-esconder-o-detalhe)

Escrever a consulta e verificar o resultado

Escreve uma consulta com uma linha por sessão e a quantidade de bilhetes pagos, ordenada pelo id da sessão. Há sessões 1, 2 e 3. Os bilhetes `(id, sessao_id, estado)` são `(10,1,'pago')`, `(11,1,'pago')` e `(12,3,'cancelado')`. A sessão 2 não tem bilhetes. Compara o resultado com os dados; a avaliação do SQL é manual.

Pista

A relação exterior precisa de conservar todas as sessões.

Mais uma pista

Filtra o estado no ON e conta uma coluna não nula do bilhete.

Solução

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

A sessão 1 tem dois bilhetes pagos. As sessões 2 e 3 não têm correspondências pagas e recebem zero. Uma alternativa válida agrega os bilhetes pagos numa subconsulta e junta essa contagem às sessões, usando `COALESCE` para os casos sem correspondência.

Confere a tua resposta:

*   Produzo uma linha para cada sessão, incluindo a sessão 2.
*   Conto apenas bilhetes pagos sem perder sessões com bilhetes cancelados.
*   Obtenho (1,2), (2,0) e (3,0), por ordem de id.

Erros frequentes

`COUNT(*)` conta a linha exterior sem correspondência como 1. Um filtro `b.estado` no `WHERE` elimina as sessões 2 e 3.

[Voltar à explicação](https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/#left-join-e-o-lugar-do-filtro)

Encontrar a receita que a junção duplicou

Há sessões 1, 2 e 3. S1 tem três bilhetes pagos, de 8, 8 e 12 euros, e duas etiquetas. S2 não tem bilhetes e tem uma etiqueta. S3 tem um bilhete pago de 5 euros e nenhuma etiqueta. As etiquetas de cada sessão são distintas.

Uma consulta faz LEFT JOIN das sessões aos bilhetes pagos e outro LEFT JOIN às etiquetas; depois agrupa por sessão e calcula `COUNT(b.id)` e `SUM(b.preco)`. Que quantidade e receita obtém para S1? `SUM(DISTINCT b.preco)` corrige a receita? Escreve a consulta que devolve uma linha por sessão com pagos, receita e quantidade de etiquetas. Usa `bilhetes(id, sessao_id, preco, estado)` e `etiquetas(sessao_id, nome)`.

Pista

Antes de somar, conta as combinações de cada bilhete com as etiquetas.

Solução

Cada um dos três bilhetes aparece duas vezes. A consulta obtém seis ocorrências e `2 × (8+8+12) = 56` euros. `SUM(DISTINCT b.preco)` soma apenas 8 e 12: dá 20 e elimina uma compra legítima. A receita certa é 28.

```
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;
```

Os resultados `(id, pagos, receita, etiquetas)` são `(1,3,28,2)`, `(2,0,0,1)` e `(3,1,5,0)`. Cada CTE tem no máximo uma linha por sessão. As junções exteriores preservam S2 e S3; o zero é a interpretação da ausência adotada por este relatório.

Confere a tua resposta:

*   Obtenho seis ocorrências e receita 56 na junção errada de S1.
*   Explico por que SUM DISTINCT daria 20 em vez de 28.
*   Agrego bilhetes e etiquetas separadamente e conservo as três sessões.

[Voltar à explicação](https://resumos.rgo.pt/cadeiras/lbaw/consultas-relacionais/#duas-rela%C3%A7%C3%B5es-podem-multiplicar-a-mesma-venda)