🔗 CAPÍTULO 15: LÓGICA NULL, SUBQUERIES E JOINS

🎯 Objetivos de Aprendizagem

Ao final deste capítulo (estimativa: 2 horas de estudo autoguiado), você será capaz de:

• 🔹 Compreender a Lógica Tri-Valente da SQL: TRUE, FALSE e UNKNOWN (NULL), e os operadores IS NULL e COALESCE().
• 🔹 Dominar todos os tipos de junção: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN e FULL OUTER JOIN.
• 🔹 Construir Subqueries aninhadas e correlacionadas com operadores IN, NOT IN, EXISTS e NOT EXISTS.
• 🔹 Mapear consultas multi-tabela com SQLAlchemy 2.0 utilizando select().join() e joinedload().

No mundo real corporativo, os dados nunca estão em uma única tabela. Eles estão espalhados por dezenas de tabelas normalizadas. O desenvolvedor sênior é aquele capaz de "costurar" essas informações em tempo real. 🛡️🧩

🏢 O Cenário Prático (Seu Desafio)

A equipe de faturamento da TecProExpress fez uma busca simples para somar todos os pacotes entregues, mas o valor bateu milhares de reais a menos do que o real. Por quê? Porque alguns pacotes ainda não tinham um Motorista designado (ID_Motorista = NULL), e o JOIN que o desenvolvedor júnior usou simplesmente deletou esses pacotes do relatório!

"Seu desafio é dominar as armadilhas do NULL e aplicar o tipo correto de JOIN para garantir que pacotes não atribuídos continuem aparecendo no faturamento geral."

🧠 Fundamentos: O Buraco Negro do NULL

No SQL, a lógica não é Binária (True/False). É Ternária (True/False/Unknown). O NULL representa o "Desconhecido". E a regra de ouro é: A matemática com o desconhecido sempre resulta em desconhecido.

• 10 + NULL = NULL
• 'A' = NULL -> FALSO (Nem o NULL é igual a outro NULL!)

🛠️ Como checar se algo é NULL?

Você não pode usar = (igual). Você deve usar a palavra IS.

-- Errado (Não retorna nada)
SELECT * FROM pacote WHERE id_motorista = NULL;

-- Certo (Traz os pacotes sem motorista)
SELECT * FROM pacote WHERE id_motorista IS NULL;

📖 Subqueries (A Consulta Inception)

Uma Subquery é um SELECT dentro de outro SELECT. Usamos isso quando precisamos do resultado de uma busca para poder fazer outra busca.

🛠️ Código do Exemplo (Quem ganha mais que a média?)

-- A Subquery (entre parênteses) roda PRIMEIRO e calcula a média. 
-- O valor calculado é passado para o SELECT de fora.
SELECT nome, salario 
FROM motorista 
WHERE salario > (SELECT AVG(salario) FROM motorista);

🔗 A Arte dos JOINs

Quando as tabelas foram normalizadas (Cap. 10), elas foram separadas. O JOIN é a "Supercola" que une elas de volta.

📊 Diagrama de Venn dos JOINs

flowchart LR
    subgraph INNER JOIN
    A1(("Tabela A")) --- B1(("Tabela B"))
    end
    
    subgraph LEFT JOIN
    A2((("Tabela A"))) --- B2(("Tabela B"))
    end
    
    style A1 fill:#e0e0e0
    style B1 fill:#e0e0e0
    style A2 fill:#4caf50
    style B2 fill:#e0e0e0

1. INNER JOIN (A Interseção Perfeita)

Retorna APENAS o que existe dos dois lados. (O erro do júnior da TecProExpress).

• Se o pacote não tem motorista, ele não aparece.

2. LEFT JOIN (Preservando a Tabela da Esquerda)

Retorna TUDO da Tabela A (Esquerda), mesmo que ela não tenha correspondência na Tabela B. (A solução para o relatório!).

• Todos os pacotes aparecem. Se não tiver motorista, a coluna do motorista vem preenchida com NULL.

🛠️ Prática Obrigatória: Salvando o Faturamento

Cenário: Corrigindo o erro do painel da TecProExpress.

1. Crie a tabela motorista e a tabela pacote.
2. Insira dois pacotes com motoristas e um pacote com id_motorista = NULL.
3. Crie uma query que liste todos os pacotes, mostrando o nome do motorista apenas quando ele existir.

🚀 Script de Seed (Gabarito de JOINs)

-- DDL
CREATE TABLE motorista (id INT PRIMARY KEY, nome VARCHAR(50));
CREATE TABLE pacote (id INT PRIMARY KEY, descricao VARCHAR(50), valor DECIMAL(10,2), id_motorista INT);

-- DML
INSERT INTO motorista VALUES (1, 'Carlos'), (2, 'Ana');
INSERT INTO pacote VALUES (100, 'Geladeira', 200.00, 1);
INSERT INTO pacote VALUES (101, 'TV', 50.00, 2);
INSERT INTO pacote VALUES (102, 'Micro-ondas', 30.00, NULL); -- Pacote sem dono

-- O ERRO DO JÚNIOR (O Micro-ondas não aparece)
-- SELECT p.descricao, m.nome FROM pacote p INNER JOIN motorista m ON p.id_motorista = m.id;

-- DQL (A SOLUÇÃO COM LEFT JOIN)
SELECT p.descricao, p.valor, m.nome AS motorista_responsavel
FROM pacote p
LEFT JOIN motorista m ON p.id_motorista = m.id;

🔍 Detalhamento da Consulta:

• pacote p: A letra p é um Alias (Apelido), para não precisarmos digitar o nome completo da tabela toda hora.
• LEFT JOIN: O pacote é a Tabela A (Esquerda, citada antes do JOIN). Logo, a query promete: "Mostrarei todos os pacotes, custe o que custar!".

💻 Ponte Prática: Do SQL Manual ao SQLAlchemy 2.0 ORM

Como o SQLAlchemy 2.0 trata a Lógica Ternária do NULL e executa LEFT JOINs e Expressões CASE com segurança?

🔴 1. A Abordagem Manual (O Perigo de INNER JOIN Cego e Comparações Nulas)

No SQL manual, o uso descuidado de INNER JOIN apaga registros que possuem campos nulos do relatório:

# ❌ ABORDAGEM COM SQL MANUAL: Pacotes sem motorista são omitidos do faturamento!
cursor.execute("""
    SELECT p.descricao, p.valor, m.nome 
    FROM pacotes p 
    INNER JOIN motoristas m ON p.motorista_id = m.id; -- ⚠️ Deleta pacotes sem motorista!
""")

🟢 2. A Abordagem com SQLAlchemy 2.0 (outerjoin(), case() e func.coalesce())

Com o SQLAlchemy 2.0, usamos select().outerjoin() garantindo que 100% dos pacotes apareçam. Tratamos os campos nulos usando func.coalesce():

# ✅ ABORDAGEM MODERNA COM SQLALCHEMY 2.0: Outer Joins e Coalesce
from sqlalchemy import create_engine, String, Float, Integer, ForeignKey, select, func, case
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

class MotoristaJoinModel(Base):
    __tablename__ = "motoristas_joins"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(50), nullable=False)

class PacoteJoinModel(Base):
    __tablename__ = "pacotes_joins"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    descricao: Mapped[str] = mapped_column(String(50), nullable=False)
    valor: Mapped[float] = mapped_column(Float, nullable=False)
    id_motorista: Mapped[int | None] = mapped_column(ForeignKey("motoristas_joins.id"), nullable=True)

    motorista: Mapped["MotoristaJoinModel" | None] = relationship("MotoristaJoinModel")

🛠️ Mini-Projeto 15 (BD): Relatório de Faturamento com Outer Joins

Objetivo: Gerar um relatório que inclui todos os pacotes (mesmo sem motorista alocado) e categoriza pacotes caros com a cláusula condicional case().

📋 Pré-requisitos e Instalação

No terminal do seu ambiente virtual (PowerShell ou Bash), instale a biblioteca necessária:

pip install sqlalchemy

💻 Código Completo e Autocontido (miniprojeto_15_joins.py)

Crie o arquivo miniprojeto_15_joins.py e insira o código abaixo integralmente:

"""
Mini-Projeto 15: Relatório de Faturamento com Outer Joins
Curso: GTI - Banco de Dados Relacionais e Engenharia de Software
Stack: Python 3.11+ | SQLAlchemy 2.0 | SQLite
"""
import os
from sqlalchemy import create_engine, String, Float, Integer, ForeignKey, select, func, case
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

# 1. Definição Declarativa dos Schemas com Chave Estrangeira Anulável
class Base(DeclarativeBase):
    pass

class MotoristaJoinModel(Base):
    __tablename__ = "motoristas_joins"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    nome: Mapped[str] = mapped_column(String(50), nullable=False)

class PacoteJoinModel(Base):
    __tablename__ = "pacotes_joins"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    descricao: Mapped[str] = mapped_column(String(50), nullable=False)
    valor: Mapped[float] = mapped_column(Float, nullable=False)
    id_motorista: Mapped[int | None] = mapped_column(ForeignKey("motoristas_joins.id"), nullable=True)

    motorista: Mapped["MotoristaJoinModel | None"] = relationship("MotoristaJoinModel")

# 2. Ponto de Entrada Executável
if __name__ == "__main__":
    DB_FILE = "tecpro_joins_demo.db"

    # Reset preventivo para garantir idempotência em testes repetidos
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    engine = create_engine(f"sqlite:///{DB_FILE}", echo=False)
    Base.metadata.create_all(bind=engine)

    # 1. Povoando a base de testes
    with Session(engine) as session:
        m1 = MotoristaJoinModel(id=1, nome="Carlos Oliveira")
        m2 = MotoristaJoinModel(id=2, nome="Ana Cristina")
        session.merge(m1)
        session.merge(m2)

        p1 = PacoteJoinModel(id=100, descricao="Geladeira Frost Free", valor=200.0, id_motorista=1)
        p2 = PacoteJoinModel(id=101, descricao="Smart TV 55'", valor=50.0, id_motorista=2)
        p3 = PacoteJoinModel(id=102, descricao="Micro-ondas Digital", valor=30.0, id_motorista=None)  # Sem motorista!
        session.merge(p1)
        session.merge(p2)
        session.merge(p3)
        session.commit()

    print("🔗 RELATÓRIO DE ENTREGAS COM LEFT OUTER JOIN - TECPROEXPRESS")
    print("=" * 70)

    with Session(engine) as session:
        # LEFT JOIN com COALESCE para substituir NULL por texto legível:
        motorista_nome = func.coalesce(MotoristaJoinModel.nome, "⚠️ [NÃO ALOCADO]")

        # Classificação condicional CASE WHEN:
        categoria_frete = case(
            (PacoteJoinModel.valor >= 100.0, "CARGA PESADA / VIP"),
            else_="CARGA CONVENCIONAL"
        )

        stmt = (
            select(
                PacoteJoinModel.id,
                PacoteJoinModel.descricao,
                PacoteJoinModel.valor,
                motorista_nome.label("responsavel"),
                categoria_frete.label("categoria")
            )
            .outerjoin(PacoteJoinModel.motorista)
        )

        for linha in session.execute(stmt):
            print(f"📦 Pacote #{linha.id}: {linha.descricao:<22} | R$ {linha.valor:>6.2f} | {linha.responsavel:<20} | {linha.categoria}")

    print("=" * 70)

🚀 Como Executar

Execute o script no terminal:

python miniprojeto_15_joins.py

🖥️ Saída Esperada no Console

🔗 RELATÓRIO DE ENTREGAS COM LEFT OUTER JOIN - TECPROEXPRESS
======================================================================
📦 Pacote #100: Geladeira Frost Free   | R$ 200.00 | Carlos Oliveira      | CARGA PESADA / VIP
📦 Pacote #101: Smart TV 55'           | R$  50.00 | Ana Cristina         | CARGA CONVENCIONAL
📦 Pacote #102: Micro-ondas Digital    | R$  30.00 | ⚠️ [NÃO ALOCADO]     | CARGA CONVENCIONAL
======================================================================

💡 Checkpoint de Lógica