Banco de Dados III — JOINs e Consultas Complexas

1. O problema: dados em tabelas separadas

Nas aulas anteriores você aprendeu a criar tabelas e aplicar as formas normais — 1FN, 2FN e 3FN. A normalização resolve problemas críticos: elimina redundância de dados, previne anomalias de inserção (ter que duplicar informação ao inserir um novo registro), anomalias de atualização (ter que alterar o mesmo dado em dezenas de linhas) e anomalias de deleção (perder dados importantes ao excluir um registro). Um banco normalizado é mais consistente, ocupa menos espaço e é mais fácil de manter.

Mas há um preço. Quando você separa informações em tabelas distintas — clientes em uma, pedidos em outra, produtos em uma terceira — você precisa de um mecanismo para juntar esses dados no momento da consulta. Sem esse mecanismo, você precisaria fazer várias consultas separadas no código da aplicação e montar os dados manualmente: consultar todos os clientes, depois consultar pedidos de cada um, e combinar os resultados em loops. Isso é lento, verboso e propenso a erros.

A solução que o SQL oferece é o JOIN: uma operação que combina linhas de duas ou mais tabelas com base em uma condição de relacionamento — geralmente a igualdade entre uma chave estrangeira e a chave primária correspondente. Em vez de múltiplas viagens ao banco, você descreve a consulta completa em um único comando SQL e deixa o mecanismo do banco de dados (o query planner) decidir a forma mais eficiente de executá-la. Essa abordagem não é só mais elegante — em tabelas grandes, a diferença de desempenho pode ser de ordens de magnitude.

O modelo de dados desta aula

Para explorar todos os tipos de JOIN, usaremos um modelo de e-commerce simplificado com quatro tabelas. Cada tabela tem uma responsabilidade clara e se relaciona com as demais via chaves estrangeiras:

Este é exatamente o padrão que você encontrará em sistemas reais. O ORDER HEADER / ORDER LINE é um dos relacionamentos mais clássicos da modelagem de dados. Dominá-lo em SQL significa que você consegue extrair qualquer informação comercialmente relevante desse tipo de estrutura — relatórios de vendas, análise de clientes, controle de estoque, tudo passa por JOINs sobre esse modelo.

2. INNER JOIN — a interseção dos conjuntos

O INNER JOIN é o JOIN mais comum e o mais intuitivo quando você pensa em termos de teoria de conjuntos. Ele retorna apenas os registros que têm correspondência nas duas tabelas envolvidas. Se um cliente não tem nenhum pedido, ele não aparece no resultado. Se um pedido tiver um cliente_id inválido (o que não deveria acontecer com uma FK correta), aquele pedido também não apareceria. O resultado é a interseção: apenas o que existe em ambos os lados.

A sintaxe do INNER JOIN usa a cláusula ON para especificar a condição de junção. É uma boa prática usar aliases (apelidos) para as tabelas — c para clientes, p para pedidos — para tornar a query mais legível e evitar ambiguidade quando as tabelas têm colunas com o mesmo nome (como id). A palavra INNER é opcional; JOIN sozinho é tratado como INNER JOIN na maioria dos SGBDs.

-- INNER JOIN: pedidos com cliente
SELECT c.nome, p.id AS pedido_id, p.total
FROM clientes c
INNER JOIN pedidos p ON c.id = p.cliente_id;

Veja como o banco executa essa query mentalmente: ele percorre todas as linhas de clientes, e para cada linha, procura linhas em pedidos onde pedidos.cliente_id é igual ao clientes.id atual. Cada combinação que satisfaça a condição gera uma linha no resultado. Um cliente com três pedidos gerará três linhas no resultado — uma para cada pedido.

Resultado visual

Supondo que o banco tenha três clientes (Ana, Bruno, Carla) e que apenas Ana e Bruno tenham pedidos, o resultado seria:

Carla Pereira não aparece pois não tem pedidos — comportamento esperado do INNER JOIN.

Quando usar o INNER JOIN

Use INNER JOIN quando você precisa de dados que necessariamente existem em ambas as tabelas e não quer ver registros incompletos no resultado. Exemplos típicos: listar pedidos com os dados do cliente (não faz sentido exibir um pedido sem saber quem é o cliente), exibir itens de um pedido com o nome do produto, gerar relatório de vendas por vendedor.

3. LEFT JOIN — todos da esquerda, com ou sem par

O LEFT JOIN (ou LEFT OUTER JOIN) resolve um problema que o INNER JOIN não consegue: "quero ver todos os registros da tabela da esquerda, mesmo que não tenham correspondência na tabela da direita". Quando não há correspondência, as colunas da tabela da direita aparecem preenchidas com NULL. A tabela "esquerda" é aquela que vem antes da cláusula LEFT JOIN.

O caso de uso mais clássico é o relatório de clientes com contagem de pedidos. Se você usar INNER JOIN, clientes sem nenhum pedido desaparecem do relatório — o que pode ser exatamente o oposto do que o negócio precisa. Talvez o objetivo seja justamente identificar esses clientes inativos para uma campanha de reativação. O LEFT JOIN preserva todos os clientes, colocando zero na contagem de pedidos para os que não compraram.

-- LEFT JOIN: todos os clientes, com ou sem pedidos
SELECT c.nome, COUNT(p.id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
GROUP BY c.id, c.nome
ORDER BY total_pedidos DESC;

Um detalhe importante: usamos COUNT(p.id) e não COUNT(*). Isso porque COUNT(*) conta linhas independentemente de NULLs, enquanto COUNT(p.id) ignora linhas onde p.id é NULL — ou seja, conta corretamente zero pedidos para o cliente sem correspondência. Se usássemos COUNT(*), clientes sem pedidos apareceriam com contagem 1 (por causa da linha com NULLs que o LEFT JOIN gera), o que seria um bug sutil e perigoso.

O truque IS NULL: encontrar ausências

Uma das aplicações mais poderosas do LEFT JOIN é encontrar registros que não existem na segunda tabela. Isso é feito combinando LEFT JOIN com um filtro WHERE coluna_direita IS NULL. Essa técnica é frequentemente mais eficiente do que usar NOT IN com subquery, especialmente quando há NULLs na lista.

-- Clientes sem nenhum pedido (LEFT JOIN + IS NULL)
SELECT c.nome
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
WHERE p.id IS NULL;

A lógica é elegante: o LEFT JOIN gera uma linha para cada cliente, com p.id = NULL para os que não têm pedidos. O WHERE p.id IS NULL filtra exatamente essas linhas. O resultado são apenas os clientes que nunca compraram. Esse padrão — LEFT JOIN + IS NULL — é uma ferramenta essencial no arsenal de qualquer desenvolvedor que trabalha com SQL.

4. RIGHT JOIN e FULL OUTER JOIN

O RIGHT JOIN é o espelho exato do LEFT JOIN: preserva todos os registros da tabela da direita (a que vem após RIGHT JOIN), mesmo que não tenham correspondência na esquerda. Na prática, o RIGHT JOIN é raramente necessário: qualquer consulta que usa RIGHT JOIN pode ser reescrita como LEFT JOIN simplesmente trocando a ordem das tabelas. Muitas equipes adotam a convenção de sempre usar LEFT JOIN para manter a consistência e legibilidade do código.

O FULL OUTER JOIN combina LEFT e RIGHT: retorna todos os registros de ambas as tabelas, colocando NULL nas colunas da outra tabela quando não há correspondência. É útil em cenários de reconciliação de dados — por exemplo, comparar duas tabelas de fontes diferentes e encontrar registros que existem em uma mas não na outra, em qualquer direção. Nem todos os SGBDs suportam FULL OUTER JOIN diretamente: MySQL, por exemplo, não tem essa sintaxe nativa, sendo necessário simular com UNION de LEFT JOIN e RIGHT JOIN.

-- FULL OUTER JOIN: todos de ambas as tabelas
SELECT c.nome, p.id AS pedido_id
FROM clientes c
FULL OUTER JOIN pedidos p ON c.id = p.cliente_id;

-- Anti-join total: registros sem par em nenhum dos lados
SELECT c.nome, p.id AS pedido_id
FROM clientes c
FULL OUTER JOIN pedidos p ON c.id = p.cliente_id
WHERE c.id IS NULL OR p.id IS NULL;

Ao escrever JOINs múltiplos — unindo três ou mais tabelas — o banco executa os JOINs da esquerda para a direita, um de cada vez. A ordem das tabelas importa para a legibilidade e, em casos específicos, pode influenciar o plano de execução. A boa prática é começar pela tabela mais restritiva (com menos linhas ou com o melhor índice) para reduzir o volume de dados processados nos JOINs subsequentes.

-- JOIN múltiplo: pedidos + cliente + produto
SELECT c.nome, pr.nome AS produto, ip.quantidade, ip.preco_unit
FROM pedidos p
INNER JOIN clientes c ON c.id = p.cliente_id
INNER JOIN itens_pedido ip ON ip.pedido_id = p.id
INNER JOIN produtos pr ON pr.id = ip.produto_id;

5. SELF JOIN — a tabela se une consigo mesma

O SELF JOIN não é um tipo especial de JOIN em termos de sintaxe — é apenas um JOIN onde as duas "tabelas" são, na verdade, a mesma tabela referenciada com aliases diferentes. Ele resolve um problema elegante: quando uma tabela contém um relacionamento hierárquico ou reflexivo dentro de si mesma.

O exemplo clássico é a tabela de funcionários. Um funcionário tem um gerente — mas o gerente também é um funcionário na mesma tabela. A coluna gerente_id é uma chave estrangeira que aponta para a própria tabela funcionarios. Para exibir o nome do funcionário junto com o nome do seu gerente, você precisa unir a tabela de funcionários com ela mesma: uma vez para o funcionário (alias f) e uma vez para o gerente (alias g).

-- Estrutura da tabela com auto-referência
CREATE TABLE funcionarios (
  id         SERIAL PRIMARY KEY,
  nome       VARCHAR(100),
  gerente_id INT REFERENCES funcionarios(id)
);

-- SELF JOIN: nome do funcionário + nome do gerente
SELECT
  f.nome AS funcionario,
  g.nome AS gerente
FROM funcionarios f
LEFT JOIN funcionarios g ON f.gerente_id = g.id;

-- Resultado esperado:
-- Carla | Ana
-- Bruno | Ana
-- Ana   | NULL  (raiz da hierarquia, sem gerente)

Note o uso de LEFT JOIN em vez de INNER JOIN: o CEO (ou qualquer raiz da hierarquia) tem gerente_id = NULL, então um INNER JOIN eliminaria esse registro — e você perderia o topo da hierarquia nos resultados. O LEFT JOIN preserva todos, mostrando NULL para o gerente de quem está no topo. Outro exemplo clássico de SELF JOIN é a tabela de categorias com subcategorias: categoria_pai_id apontando para a própria tabela de categorias.

6. GROUP BY e funções de agregação com JOIN

A combinação de JOIN com GROUP BY é onde as consultas SQL se tornam verdadeiramente poderosas para análise de dados. Você une as tabelas para ter acesso a todas as colunas relevantes e, em seguida, agrupa e resume esses dados para responder perguntas de negócio: "qual é o total de vendas por cliente?", "quais categorias têm mais produtos acima de determinado preço?", "qual vendedor fechou mais pedidos no último trimestre?"

As funções de agregação mais usadas são COUNT(), SUM(), AVG(), MIN() e MAX(). Quando combinadas com GROUP BY, elas operam sobre cada grupo de linhas, produzindo um único valor resumido por grupo. Uma regra fundamental: toda coluna no SELECT que não é uma função de agregação deve aparecer na cláusula GROUP BY — caso contrário, o banco de dados não saberá qual valor exibir para as linhas do grupo.

-- GROUP BY + HAVING: categorias com mais de 3 produtos acima de R$100
SELECT categoria, COUNT(*) AS qtd, AVG(preco) AS media
FROM produtos
WHERE preco > 100
GROUP BY categoria
HAVING COUNT(*) > 3
ORDER BY media DESC;

O exemplo acima demonstra a sequência lógica de uma query com GROUP BY: primeiro o WHERE filtra linhas individuais (apenas produtos com preço acima de R$100), depois o GROUP BY agrupa por categoria, depois o HAVING filtra grupos (apenas categorias com mais de 3 produtos nessa faixa de preço), e finalmente o ORDER BY ordena o resultado. Entender essa ordem de execução é fundamental para escrever queries corretas.

7. WHERE vs HAVING — antes e depois do agrupamento

A distinção entre WHERE e HAVING é uma das mais importantes — e mais frequentemente confundidas — em SQL. A regra é simples: WHERE filtra linhas individuais antes do agrupamento, enquanto HAVING filtra grupos depois do agrupamento. O WHERE nunca pode referenciar funções de agregação (como COUNT ou SUM), porque o agrupamento ainda não aconteceu nesse ponto da execução. O HAVING é exatamente para isso: filtrar com base em agregações.

Para solidificar a diferença, considere dois problemas distintos. Primeiro: "quais clientes fizeram mais de 5 pedidos?" — isso é um filtro sobre o resultado agregado, portanto vai no HAVING. Segundo: "quais pedidos de valor acima de R$500 foram feitos por clientes de São Paulo?" — isso envolve filtros sobre colunas individuais antes de qualquer agrupamento, portanto vai no WHERE. Às vezes você precisa dos dois: WHERE para pré-filtrar linhas, HAVING para pós-filtrar grupos.

-- WHERE: filtra ANTES do agrupamento
-- "dentre os pedidos de 2024, quais clientes compraram mais de 3 vezes?"
SELECT c.nome, COUNT(p.id) AS total
FROM clientes c
INNER JOIN pedidos p ON c.id = p.cliente_id
WHERE p.criado_em >= '2024-01-01'    -- filtra linhas antes de agrupar
GROUP BY c.id, c.nome
HAVING COUNT(p.id) > 3;            -- filtra grupos após agrupar

-- Erro comum: usar COUNT no WHERE (não é permitido)
-- WHERE COUNT(p.id) > 3   <-- ERRO: aggregate não pode aparecer no WHERE

Do ponto de vista de desempenho, aplicar filtros no WHERE é sempre preferível ao HAVING quando possível, porque o WHERE reduz o volume de dados antes de o banco fazer o trabalho de agrupamento. Filtrar depois de agrupar (HAVING) significa que o banco já processou todos os dados. Portanto, se um filtro pode ser expresso tanto com WHERE quanto com HAVING sem mudança de semântica, prefira o WHERE.

8. Subconsultas — queries dentro de queries

Uma subconsulta (ou subquery) é uma consulta SQL aninhada dentro de outra consulta. Elas permitem expressar problemas complexos de forma modular: você resolve um sub-problema interno e usa o resultado como parte da consulta principal. Subconsultas podem aparecer em três posições principais: no WHERE (como lista de valores ou condição booleana), no FROM (como uma tabela derivada, também chamada de derived table ou inline view), e no SELECT (como valor escalar).

A forma mais simples é a subconsulta no WHERE com IN. Ela responde "quais clientes estão na lista de clientes que fizeram pedidos acima de R$1.000?" sem precisar fazer um JOIN explícito. O operador IN verifica se o valor está em um conjunto retornado pela subconsulta. A subconsulta é executada uma vez, e seu resultado é usado como uma lista de valores para o filtro da consulta externa.

-- Subquery com IN
SELECT nome FROM clientes
WHERE id IN (
  SELECT DISTINCT cliente_id FROM pedidos WHERE total > 1000
);

O operador EXISTS é frequentemente uma alternativa mais eficiente ao IN, especialmente quando a subconsulta pode retornar muitos valores ou quando há NULLs envolvidos. Ao contrário do IN, o EXISTS não precisa coletar todos os resultados da subconsulta — ele para assim que encontra a primeira correspondência. A subconsulta dentro do EXISTS usa um SELECT 1 por convenção (você não precisa de dados, apenas verificar se a linha existe), e referencia colunas da consulta externa — tornando-a uma subconsulta correlacionada: ela é executada uma vez para cada linha da consulta externa.

-- EXISTS vs IN (performático)
SELECT nome FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.cliente_id = c.id          -- correlação com a query externa
    AND p.total > 1000
);

A terceira forma importante é a tabela derivada: uma subconsulta no FROM que se comporta como uma tabela temporária. Isso permite fazer um JOIN com o resultado de uma consulta agregada. Por exemplo: para listar clientes com seu total de pedidos lado a lado com a média geral de todos os clientes, você pode usar uma subconsulta no FROM que calcula a média, e fazer JOIN dessa tabela derivada com a tabela de clientes. Nos bancos de dados modernos, as CTEs (WITH nome AS (...)) são uma alternativa mais legível às tabelas derivadas aninhadas.

9. Window Functions — agregação sem colapso de linhas

As window functions (funções de janela) resolvem um problema que o GROUP BY não consegue: calcular uma agregação por grupo e ainda assim manter as linhas individuais no resultado. Quando você usa GROUP BY + COUNT, você colapsa várias linhas em uma. Com uma window function, você pode calcular o COUNT por grupo e exibir esse valor em cada linha do grupo, sem colapsar nada. Isso é extremamente útil em relatórios analíticos.

A sintaxe usa a cláusula OVER: você especifica sobre qual "janela" (subconjunto de linhas) a função deve operar. A janela pode ser definida por PARTITION BY (equivalente a agrupar) e ORDER BY (para funções que dependem de ordem, como ranking e acumuladores). ROW_NUMBER() atribui um número sequencial a cada linha dentro da partição. RANK() faz o mesmo, mas dá o mesmo rank para empates (com "saltos" nos números seguintes). DENSE_RANK() é igual ao RANK, mas sem saltos.

-- ROW_NUMBER: ranking de pedidos por cliente (por valor decrescente)
SELECT
  c.nome,
  p.id AS pedido_id,
  p.total,
  ROW_NUMBER() OVER (
    PARTITION BY c.id
    ORDER BY p.total DESC
  ) AS ranking_cliente
FROM clientes c
INNER JOIN pedidos p ON c.id = p.cliente_id;

-- LAG: valor do pedido anterior do mesmo cliente
SELECT
  c.nome,
  p.criado_em,
  p.total,
  LAG(p.total) OVER (
    PARTITION BY c.id
    ORDER BY p.criado_em
  ) AS pedido_anterior
FROM clientes c
INNER JOIN pedidos p ON c.id = p.cliente_id;

-- SUM acumulado: total acumulado de vendas por mes
SELECT
  DATE_TRUNC('month', criado_em) AS mes,
  SUM(total) AS vendas_mes,
  SUM(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', criado_em)) AS acumulado
FROM pedidos
GROUP BY mes
ORDER BY mes;

LAG(coluna) e LEAD(coluna) permitem acessar o valor da linha anterior e da linha seguinte na janela ordenada, respectivamente. Isso é particularmente útil para calcular variações: "quanto cresceu este pedido em relação ao anterior do mesmo cliente?", "qual a diferença de preço entre este produto e o produto seguinte da mesma categoria?". Essas análises seriam extremamente complexas com JOIN e GROUP BY tradicionais, mas são naturais com window functions.

10. Modelagem estática com diagramas de classes

Até aqui você modelou os dados do sistema com diagramas Entidade–Relacionamento (ER) e os exercitou em SQL via JOINs. Existe uma segunda forma de modelagem, complementar e mais rica em comportamento: o diagrama de classes da UML (Unified Modeling Language). Enquanto o ER descreve como os dados são guardados, o diagrama de classes descreve como o domínio é representado em código orientado a objetos — incluindo atributos, operações, visibilidade, herança e o tipo exato de cada associação.

A UML divide a modelagem em duas categorias amplas: a modelagem estática, que captura a estrutura do sistema em um determinado instante (classes, atributos, relacionamentos) — e a modelagem dinâmica, que captura o comportamento ao longo do tempo (diagramas de sequência, de estados, de atividades, vistos na Aula 3). O diagrama de classes é o principal artefato da modelagem estática e serve como ponte entre o levantamento de requisitos, o desenho do banco de dados e a implementação em código.

Anatomia de uma classe

Uma classe é representada por um retângulo dividido em três compartimentos: nome (no topo, em negrito e em maiúsculo), atributos (no meio, com tipo e visibilidade) e operações/métodos (na base, com parâmetros e tipo de retorno). Compartimentos vazios podem ser omitidos quando não forem relevantes para a leitura do diagrama.

Visibilidade — quem vê o quê

A visibilidade controla o acesso a atributos e operações a partir de outras classes. Os quatro símbolos da UML correspondem diretamente aos modificadores de quase todas as linguagens orientadas a objetos:

O diagrama abaixo mostra uma classe Cliente com os três compartimentos preenchidos. Note como cada atributo é privado (−) e cada operação é pública (+) — esse é o padrão de encapsulamento clássico: esconde dados, expõe comportamento.

Tipos de relacionamento

Diagramas de classes distinguem com precisão diferentes tipos de relação entre classes — algo que o ER tradicional não captura com tanto detalhe. Cada notação carrega semântica específica e influencia decisões de implementação (ciclo de vida do objeto, propagação de exclusão, navegabilidade).

Multiplicidade — cardinalidade nas pontas

A multiplicidade indica quantas instâncias de uma classe podem participar da associação. É escrita em cada extremidade da linha e é o equivalente direto da cardinalidade do ER (1:1, 1:N, N:M).

O modelo de e-commerce desta aula em UML

O mesmo modelo que vimos como tabelas relacionais e usamos nos JOINs pode ser desenhado como diagrama de classes. Compare a riqueza semântica: agora vemos não apenas que existe FK entre itens_pedido e pedidos, mas que se trata de uma composição — um item não tem sentido fora do pedido que o contém. Já Cliente → Pedido e ItemPedido → Produto são apenas associações: clientes existem antes do primeiro pedido, e produtos existem mesmo sem nenhum item de pedido referenciá-los.

Leia o diagrama em três passos. Primeiro, os relacionamentos: a seta --> mostra navegabilidade (a partir de quem você consegue chegar a quem). Segundo, as multiplicidades nas pontas: um cliente realiza 0..* pedidos, ou seja, pode existir sem nenhum pedido. Terceiro, a forma da ponta da linha: o losango preto entre Pedido e ItemPedido indica composição — o ciclo de vida do ItemPedido está atrelado ao do Pedido.

Classes abstratas, herança e interfaces

A modelagem estática brilha quando há tipos relacionados no domínio. Imagine que o sistema precise representar tanto clientes quanto funcionários — ambos são pessoas e compartilham nome, CPF e e-mail, mas cada um tem atributos e comportamentos próprios. A UML resolve isso com generalização (herança) e classes abstratas:

O estereótipo <<abstract>> indica que Pessoa não pode ser instanciada diretamente — só via subclasses concretas. Os atributos protected (#) ficam visíveis para as subclasses. A interface Notificavel aparece com o estereótipo <<interface>> e é realizada (linha tracejada) por ambas as subclasses — define um contrato comum (notificar()) sem impor uma implementação.

Do diagrama de classes ao esquema relacional

Bancos relacionais não falam UML — falam tabelas, colunas e chaves estrangeiras. O processo de transformar um diagrama de classes em DDL segue regras consistentes que você aplicará várias vezes ao longo do curso:

Boas práticas ao desenhar diagramas de classes

• Substantivos para classes, verbos para operações. Pedido é classe; confirmar() é operação. Confirmacao só faz sentido como classe se houver realmente uma entidade independente.
• Evite a "classe deus" com dezenas de atributos e operações. Se uma classe começa a fazer mais de uma coisa coesa, divida — princípio de responsabilidade única.
• Modele a multiplicidade de verdade. Não use * por preguiça; pergunte "quantos no mínimo?" e "quantos no máximo?". 1..* versus 0..* tem impacto direto no esquema do banco e nas validações de aplicação.
• Composição é compromisso forte. Use composição apenas quando o ciclo de vida realmente está atrelado. Em caso de dúvida, prefira agregação ou associação simples.
• Mostre só o relevante. Um diagrama com 30 classes ilegíveis comunica menos que três diagramas focados em diferentes subsistemas (módulo de vendas, módulo de estoque, módulo de pagamento).
• Sincronize código e diagrama. Diagramas que divergem do código se tornam folclore. Versione-os no repositório (Mermaid em Markdown é excelente para isso) e atualize sempre que a estrutura mudar.

Ponte para o Back-End I

Esta seção encerra o bloco de dados do módulo. A partir da próxima aula entramos no bloco de Back-End, e o diagrama de classes deixa de ser apenas documentação para virar a espinha dorsal do código que escreveremos em Node.js. No padrão arquitetural MVC, cada classe concreta do diagrama vira um Model — um arquivo JavaScript responsável por encapsular o acesso a uma tabela do banco. As operações que aparecem no compartimento inferior das classes (+listarPedidos(), +confirmar(), +calcularTotal()) viram métodos desse Model — e a maioria delas executará um dos JOINs que você aprendeu nesta aula.

As associações também se materializam no código. Uma associação Cliente → Pedido com multiplicidade 1..* vira um método Cliente.listarPedidos() que executa um SELECT ... FROM pedidos WHERE cliente_id = $1. Uma composição Pedido → ItemPedido orienta a estratégia de inserção e exclusão em cascata. E quando houver herança no domínio (Pessoa → Cliente, Funcionario), cabe à camada de Model decidir entre uma única tabela com coluna discriminadora ou tabelas separadas — uma decisão de mapeamento objeto-relacional (ORM) que estudaremos a seguir.

11. RM-ODP — JOINs nas visões do sistema

O framework RM-ODP nos convida a analisar o sistema a partir de cinco perspectivas complementares. Os JOINs e consultas complexas que aprendemos nesta aula têm um papel central em pelo menos duas dessas visões: a visão de Informação e a visão Computacional.

A visão de Informação define a estrutura dos dados do sistema: entidades, atributos e relacionamentos. O diagrama ER que você desenhou nas aulas anteriores é a expressão visual desta visão. Os JOINs são precisamente a forma como o SQL navega os relacionamentos desse modelo: cada ON c.id = p.cliente_id percorre exatamente a seta de relacionamento desenhada entre clientes e pedidos. Isso significa que o modelo ER não é apenas documentação — ele especifica diretamente quais JOINs serão necessários.

Ao projetar uma nova rota ou endpoint que precisará de dados de múltiplas tabelas, a primeira pergunta que você deve fazer é: "quais relacionamentos do modelo ER eu preciso navegar?" Essa pergunta traduz diretamente nos JOINs necessários. É a visão de Informação guiando a visão Computacional.

12. RF, RN e RNF aplicados a consultas complexas

Cada consulta que você escreve pode ser rastreada a um requisito. Entender essa ligação torna o código mais justificável e facilita decisões de otimização: você prioriza a performance das queries que atendem a requisitos funcionais críticos, e documenta as garantias de qualidade como requisitos não funcionais mensuráveis.

Requisitos Funcionais (RF)

Requisitos funcionais descrevem o que o sistema deve fazer. Consultas complexas frequentemente implementam diretamente RFs de reporting e visualização. Exemplos de RFs mapeados a consultas desta aula:

Regras de Negócio (RN)

Regras de negócio são restrições e políticas do domínio que as consultas devem respeitar. Elas aparecem como filtros, condições e lógicas específicas nas queries. Por exemplo: "RN-03: Um pedido somente é considerado faturado se seu status for 'pago' ou 'entregue'" — isso se traduz em um filtro WHERE p.status IN ('pago', 'entregue') em toda query de relatório financeiro. Regras de negócio violadas em queries geram relatórios incorretos, mesmo que o SQL seja sintaticamente válido.

Requisitos Não Funcionais (RNF)

13. Estudo de caso integrado: o checkout do e-commerce

Esta seção amarra todos os conceitos da aula em um único caso, mostrando o mesmo problema sob cinco lentes diferentes: requisitos → modelagem conceitual → modelagem lógica → diagrama de classes (modelagem estática) → diagrama de sequência (modelagem dinâmica) → código SQL. Cada artefato é uma tradução do anterior, em um nível de detalhe maior, até chegar ao código que de fato roda no banco. É exatamente esse fluxo que você seguirá nos projetos do módulo.

Cenário e requisitos

Estamos construindo o endpoint de checkout de uma loja virtual. Quando o cliente confirma o pedido, o sistema precisa: (1) validar se há estoque para todos os itens; (2) criar o pedido; (3) registrar cada item; (4) reduzir o estoque dos produtos; (5) calcular o total e devolver a confirmação. Tudo isso em uma única transação — se qualquer passo falhar, nada deve ser persistido.

1 · Modelagem conceitual — o ER de alto nível

O modelo conceitual é o primeiro recorte do domínio: identifica as entidades e os relacionamentos, sem se preocupar com tipos de dado, chaves estrangeiras ou implementação. Serve para alinhar o time com o cliente: "estamos falando de quais coisas e como elas se relacionam?".

Leia as cardinalidades: ||--o{ significa "um para zero ou muitos" (um cliente pode realizar zero ou mais pedidos); ||--|{ significa "um para um ou muitos" (um pedido contém pelo menos um item). O modelo conceitual já carrega a regra de negócio RN-03: pedido sem itens é proibido.

2 · Modelagem lógica — atributos, tipos e chaves

O modelo lógico detalha cada entidade com seus atributos, tipos de dado, chaves primárias (PK), chaves estrangeiras (FK) e restrições de unicidade (UK). É a ponte entre o conceito e o código: ainda independente de SGBD, mas já com a estrutura que o banco terá. Aqui aparecem as decisões de normalização (1FN, 2FN, 3FN) que você estudou nas aulas anteriores.

Note três decisões importantes: (1) email é único (UK) — não permitiremos duas contas com o mesmo e-mail; (2) preco_unitario aparece duplicado em item_pedido mesmo já existindo em produto, atendendo a RN-02 (preço congelado no momento da compra); (3) cliente_id em pedido é a chave estrangeira que materializa o relacionamento "realiza".

3 · Modelagem estática — diagrama de classes

O diagrama de classes pega o modelo lógico e adiciona comportamento. Cada entidade vira uma classe com atributos privados e operações públicas. As multiplicidades e tipos de associação (composição, navegabilidade) ficam explícitos. Este é o esqueleto sobre o qual o Back-End será construído.

O losango preto entre Pedido e ItemPedido indica composição: itens não existem fora do pedido. Isso vai ditar o ON DELETE CASCADE no SQL. O método Produto.temEstoque(qtd) implementa diretamente a regra RN-01 — antes de aceitar o item, perguntamos ao produto se ele suporta a quantidade pedida.

4 · Modelagem dinâmica — diagrama de sequência

Enquanto o diagrama de classes mostra a estrutura parada, o diagrama de sequência mostra como ela se mexe ao longo do tempo. Cada seta horizontal é uma chamada; cada barra vertical é o tempo durante o qual um objeto está ativo. As caixas de alt e loop capturam decisões e repetições.

Observe três coisas. Primeiro, o BEGIN/COMMIT/ROLLBACK envolvendo todo o fluxo materializa o requisito RNF-01 (atomicidade). Segundo, o FOR UPDATE no SELECT bloqueia a linha do produto até o final da transação — sem ele, dois clientes simultâneos poderiam comprar o último item em estoque. Terceiro, cada interação que cruza a borda Controller → Banco é precisamente um comando SQL que vamos escrever no próximo passo.

5 · Implementação física — código SQL final

Agora descemos para o código que de fato executa no PostgreSQL. O DDL cria as tabelas com tipos exatos, restrições e índices. O DML implementa o checkout dentro de uma transação. As queries de leitura usam JOINs que percorrem exatamente as associações desenhadas no diagrama de classes.

-- Tabela clientes
CREATE TABLE clientes (
  id            SERIAL PRIMARY KEY,
  nome          VARCHAR(100) NOT NULL,
  email         VARCHAR(150) NOT NULL UNIQUE,
  data_cadastro DATE         NOT NULL DEFAULT CURRENT_DATE,
  ativo         BOOLEAN      NOT NULL DEFAULT TRUE
);

-- Tabela produtos com checks
CREATE TABLE produtos (
  id      SERIAL PRIMARY KEY,
  nome    VARCHAR(150)  NOT NULL,
  preco   NUMERIC(10,2) NOT NULL CHECK (preco > 0),
  estoque INTEGER       NOT NULL DEFAULT 0 CHECK (estoque >= 0)
);

-- Tabela pedidos com FK e indice por cliente_id (RNF-02)
CREATE TABLE pedidos (
  id            SERIAL PRIMARY KEY,
  cliente_id    INTEGER      NOT NULL REFERENCES clientes(id) ON DELETE RESTRICT,
  data_criacao  TIMESTAMP    NOT NULL DEFAULT NOW(),
  status        VARCHAR(20)  NOT NULL DEFAULT 'pendente',
  total         NUMERIC(10,2) NOT NULL DEFAULT 0
);
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);

-- Tabela de junção: composição -> ON DELETE CASCADE
CREATE TABLE itens_pedido (
  id             SERIAL PRIMARY KEY,
  pedido_id      INTEGER      NOT NULL REFERENCES pedidos(id)  ON DELETE CASCADE,
  produto_id     INTEGER      NOT NULL REFERENCES produtos(id) ON DELETE RESTRICT,
  quantidade     INTEGER      NOT NULL CHECK (quantidade > 0),
  preco_unitario NUMERIC(10,2) NOT NULL,
  UNIQUE (pedido_id, produto_id)
);
CREATE INDEX idx_itens_pedido_pedido  ON itens_pedido(pedido_id);
CREATE INDEX idx_itens_pedido_produto ON itens_pedido(produto_id);

Cada cláusula do DDL é justificada por algum elemento do modelo: UNIQUE (pedido_id, produto_id) impede o mesmo produto aparecer duas vezes no mesmo pedido (use a coluna quantidade para isso); o ON DELETE CASCADE em itens_pedido.pedido_id implementa a composição UML; o ON DELETE RESTRICT em cliente_id e produto_id impede apagar entidades referenciadas — é a "associação" do diagrama, mais frouxa que composição.

-- Checkout: cliente 1 compra 2 unidades do produto 10 e 1 do produto 22
BEGIN;

-- 1. trava as linhas dos produtos durante a transação (evita race condition)
SELECT id, estoque, preco
  FROM produtos
 WHERE id IN (10, 22)
 FOR UPDATE;

-- 2. cria o pedido (cabeçalho)
INSERT INTO pedidos (cliente_id, status)
VALUES (1, 'pendente')
RETURNING id INTO @pedido_id;  -- ou usar currval('pedidos_id_seq')

-- 3. registra cada item (preço congelado pela RN-02)
INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario)
VALUES
  (@pedido_id, 10, 2, (SELECT preco FROM produtos WHERE id = 10)),
  (@pedido_id, 22, 1, (SELECT preco FROM produtos WHERE id = 22));

-- 4. reduz estoque (RF-03)
UPDATE produtos SET estoque = estoque - 2 WHERE id = 10;
UPDATE produtos SET estoque = estoque - 1 WHERE id = 22;

-- 5. atualiza o total agregado do pedido (subquery)
UPDATE pedidos
   SET total = (SELECT SUM(quantidade * preco_unitario)
                  FROM itens_pedido
                 WHERE pedido_id = @pedido_id),
       status = 'confirmado'
 WHERE id = @pedido_id;

COMMIT;

Cada passo do DML é numerado para coincidir com as setas do diagrama de sequência. O FOR UPDATE implementa o lock de linha que evita corrida entre dois checkouts simultâneos — sem ele, dois clientes poderiam ler "estoque = 1" ao mesmo tempo e ambos confirmarem a venda. Se em qualquer ponto um CHECK falhar (estoque ficaria negativo, por exemplo), o BEGIN/COMMIT faz rollback automático e nada é persistido.

-- Detalhe completo de um pedido — todas as 4 tabelas, 1 query só
SELECT
  p.id            AS pedido_id,
  p.data_criacao,
  p.status,
  p.total,
  c.nome          AS cliente,
  c.email,
  pr.nome         AS produto,
  ip.quantidade,
  ip.preco_unitario,
  (ip.quantidade * ip.preco_unitario) AS subtotal
FROM pedidos p
INNER JOIN clientes     c  ON c.id  = p.cliente_id
INNER JOIN itens_pedido ip ON ip.pedido_id = p.id
INNER JOIN produtos     pr ON pr.id = ip.produto_id
WHERE p.id = $1
ORDER BY pr.nome;

-- Relatório agregado: vendas por cliente (LEFT JOIN + GROUP BY)
SELECT
  c.nome,
  COUNT(DISTINCT p.id)             AS total_pedidos,
  COALESCE(SUM(p.total), 0)         AS faturamento,
  COALESCE(AVG(p.total), 0)         AS ticket_medio
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id AND p.status = 'confirmado'
GROUP BY c.id, c.nome
ORDER BY faturamento DESC;

A query GET /pedidos/:id é a tradução literal das três associações do diagrama de classes: cada INNER JOIN percorre exatamente uma seta UML. O relatório de vendas usa LEFT JOIN + COUNT DISTINCT + COALESCE para incluir clientes sem nenhum pedido (com faturamento zero) — é o uso clássico do anti-join opcional que vimos na seção 3.

14. Checklist de Estudo

Clique nos itens à medida que você dominar cada conceito:

• ✓
  Consigo explicar por que a normalização exige JOINs para consultar dados relacionados.
• ✓
  Consigo escrever um INNER JOIN entre duas tabelas com a condição ON correta e usar aliases.
• ✓
  Consigo explicar a diferença de resultado entre INNER JOIN e LEFT JOIN com um exemplo concreto.
• ✓
  Consigo usar LEFT JOIN + IS NULL para encontrar registros sem correspondência (anti-join).
• ✓
  Consigo escrever um JOIN múltiplo unindo 3 ou 4 tabelas em uma única query.
• ✓
  Consigo usar SELF JOIN para consultar hierarquias (funcionário + gerente) na mesma tabela.
• ✓
  Consigo combinar JOIN com GROUP BY e funções de agregação (COUNT, SUM, AVG).
• ✓
  Consigo explicar a diferença entre WHERE e HAVING e quando usar cada um.
• ✓
  Consigo escrever uma subquery com IN e uma com EXISTS e explicar a diferença de comportamento.
• ✓
  Consigo usar ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) e explicar o que cada parte faz.
• ✓
  Consigo ler um diagrama de classes UML, identificando atributos, operações e visibilidade.
• ✓
  Sei distinguir associação, agregação, composição, generalização e realização — e a semântica de cada uma.
• ✓
  Consigo traduzir um diagrama de classes em esquema relacional, mapeando classes em tabelas, atributos em colunas e associações em chaves estrangeiras.
• ✓
  Consigo identificar qual visão do RM-ODP é mais relevante para o design de consultas SQL.
• ✓
  Sei por que é importante criar índices em colunas de chave estrangeira usadas em JOINs frequentes.
• ✓
  Consigo modelar um caso ponta-a-ponta: requisitos → ER conceitual → ER lógico → diagrama de classes → diagrama de sequência → DDL/DML SQL.
• ✓
  Entendo por que o checkout precisa rodar em transação com FOR UPDATE para evitar corrida no estoque.

Referências

• PostgreSQL Docs — Table Expressions e JOINs — documentação oficial do PostgreSQL sobre JOIN, GROUP BY e HAVING
• PostgreSQL Docs — Window Functions — referência completa de ROW_NUMBER, RANK, LAG, LEAD e agregações de janela
• Use The Index, Luke! — guia prático sobre índices e performance de queries SQL (gratuito e em inglês)
• PostgreSQL Docs — Using EXPLAIN — como interpretar o plano de execução de queries com EXPLAIN ANALYZE
• ISO/IEC 9075 — SQL Standard — padrão ANSI/ISO do SQL (SQL:2016)
• ISO/IEC 10746 — RM-ODP — Reference Model for Open Distributed Processing
• PostgreSQL Docs — Indexes — tipos de índice, criação e estratégias de uso para otimizar JOINs