FULL OUTER JOIN e CROSS JOIN

1. Introdução aos JOINs Avançados

1.1. Revisão rápida dos JOINs básicos (INNER, LEFT, RIGHT)

Antes de mergulharmos nos JOINs avançados, é fundamental relembrar os tipos básicos:

  • INNER JOIN: Retorna apenas registros que possuem correspondência em ambas as tabelas
  • LEFT JOIN: Retorna todos os registros da tabela esquerda e os correspondentes da direita
  • RIGHT JOIN: Retorna todos os registros da tabela direita e os correspondentes da esquerda

1.2. Quando os JOINs tradicionais não são suficientes

Os JOINs tradicionais apresentam limitações quando precisamos:
- Visualizar dados completos de ambas as tabelas simultaneamente
- Identificar registros órfãos em ambos os lados da relação
- Gerar todas as combinações possíveis entre conjuntos de dados

1.3. Visão geral: FULL OUTER JOIN vs. CROSS JOIN

O FULL OUTER JOIN combina o comportamento do LEFT e RIGHT JOIN, retornando todos os registros de ambas as tabelas. Já o CROSS JOIN cria um produto cartesiano, combinando cada linha de uma tabela com todas as linhas da outra.

2. FULL OUTER JOIN: Unindo Todos os Registros

2.1. Sintaxe básica e funcionamento do FULL OUTER JOIN

SELECT colunas
FROM tabela1
FULL OUTER JOIN tabela2
ON tabela1.chave = tabela2.chave;

O FULL OUTER JOIN retorna:
- Registros correspondentes (como INNER JOIN)
- Registros da tabela esquerda sem correspondência (como LEFT JOIN)
- Registros da tabela direita sem correspondência (como RIGHT JOIN)

2.2. Diferenças práticas entre FULL OUTER JOIN e LEFT/RIGHT JOIN

Enquanto LEFT JOIN prioriza a tabela esquerda e RIGHT JOIN prioriza a direita, o FULL OUTER JOIN trata ambas as tabelas igualmente. Isso é crucial quando nenhuma das tabelas é "principal".

2.3. Tratamento de valores NULL em registros sem correspondência

Registros sem correspondência em uma das tabelas terão valores NULL nas colunas da tabela oposta.

SELECT 
    COALESCE(t1.id, t2.id) AS id_unificado,
    t1.nome,
    t2.descricao
FROM tabela1 t1
FULL OUTER JOIN tabela2 t2
ON t1.id = t2.id;

3. Casos de Uso do FULL OUTER JOIN

3.1. Comparação de dados entre duas tabelas

-- Comparando clientes e seus pedidos
SELECT 
    c.cliente_id,
    c.nome AS nome_cliente,
    p.pedido_id,
    p.data_pedido
FROM clientes c
FULL OUTER JOIN pedidos p
ON c.cliente_id = p.cliente_id;

3.2. Identificação de registros órfãos em ambos os lados

SELECT 
    e.funcionario_id,
    e.nome,
    d.departamento_id,
    d.nome_departamento
FROM funcionarios e
FULL OUTER JOIN departamentos d
ON e.departamento_id = d.departamento_id
WHERE e.funcionario_id IS NULL 
   OR d.departamento_id IS NULL;

3.3. Mesclagem completa de listas de fontes diferentes

-- Unindo catálogos de produtos de duas filiais
SELECT 
    COALESCE(f1.produto_id, f2.produto_id) AS produto_id,
    f1.estoque_filial1,
    f2.estoque_filial2
FROM filial1_estoque f1
FULL OUTER JOIN filial2_estoque f2
ON f1.produto_id = f2.produto_id;

4. CROSS JOIN: Produto Cartesiano entre Tabelas

4.1. Sintaxe explícita (CROSS JOIN) vs. implícita (FROM tabela1, tabela2)

-- Sintaxe explícita (recomendada)
SELECT *
FROM tabela1
CROSS JOIN tabela2;

-- Sintaxe implícita (menos clara)
SELECT *
FROM tabela1, tabela2;

4.2. Como funciona o produto cartesiano

Cada linha da primeira tabela é combinada com todas as linhas da segunda tabela. Se a tabela A tem 3 registros e a tabela B tem 4 registros, o resultado terá 12 registros.

-- Exemplo visual
-- Tabela A: [A1, A2, A3]
-- Tabela B: [B1, B2]
-- Resultado: [A1-B1, A1-B2, A2-B1, A2-B2, A3-B1, A3-B2]

4.3. Impacto no tamanho do resultado (N x M registros)

-- Atenção: com tabelas grandes, o resultado pode ser enorme
-- Tabela A: 10.000 registros
-- Tabela B: 5.000 registros
-- Resultado: 50.000.000 registros!

5. Aplicações Práticas do CROSS JOIN

5.1. Geração de combinações de dados

-- Gerando todas as combinações de cores e tamanhos
SELECT 
    c.nome_cor,
    t.nome_tamanho,
    CONCAT(c.nome_cor, ' - ', t.nome_tamanho) AS variacao_produto
FROM cores c
CROSS JOIN tamanhos t;

5.2. Criação de calendários ou séries temporais completas

-- Gerando todos os dias de um mês para cada loja
WITH dias AS (
    SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day'::interval) AS data
)
SELECT 
    l.loja_id,
    l.nome_loja,
    d.data
FROM lojas l
CROSS JOIN dias d;

5.3. Preenchimento de lacunas em dados esparsos

-- Garantindo que todas as combinações de mês/departamento existam
SELECT 
    m.mes,
    d.departamento_id,
    COALESCE(v.vendas, 0) AS total_vendas
FROM (VALUES (1),(2),(3),(4)) AS m(mes)
CROSS JOIN departamentos d
LEFT JOIN vendas v 
    ON v.mes = m.mes 
    AND v.departamento_id = d.departamento_id;

6. Comparação Direta: FULL OUTER JOIN vs. CROSS JOIN

6.1. Critério de junção

  • FULL OUTER JOIN: Requer condição de junção explícita (ON)
  • CROSS JOIN: Não possui condição de junção

6.2. Quantidade de registros resultantes

  • FULL OUTER JOIN: No máximo (N + M) registros
  • CROSS JOIN: Exatamente N × M registros

6.3. Performance e cuidados

-- FULL OUTER JOIN: Geralmente eficiente com índices adequados
-- CROSS JOIN: Pode causar explosão de dados e consumo excessivo de memória

7. Exemplos Práticos com SQL

7.1. FULL OUTER JOIN: consulta de funcionários e departamentos

SELECT 
    e.nome AS funcionario,
    e.salario,
    d.nome AS departamento,
    d.orcamento
FROM funcionarios e
FULL OUTER JOIN departamentos d
ON e.departamento_id = d.id
ORDER BY 
    CASE WHEN e.nome IS NULL THEN 1 ELSE 0 END,
    e.nome;

7.2. CROSS JOIN: gerando todas as combinações de produtos e lojas

SELECT 
    p.nome_produto,
    l.nome_loja,
    COALESCE(es.estoque, 0) AS quantidade_estoque
FROM produtos p
CROSS JOIN lojas l
LEFT JOIN estoque es
    ON es.produto_id = p.id 
    AND es.loja_id = l.id
ORDER BY p.nome_produto, l.nome_loja;

7.3. Combinando FULL OUTER JOIN com filtros WHERE e ORDER BY

SELECT 
    COALESCE(c.nome, 'Sem cliente') AS cliente,
    COALESCE(p.id::text, 'Sem pedido') AS pedido,
    p.valor,
    p.data_pedido
FROM clientes c
FULL OUTER JOIN pedidos p
ON c.id = p.cliente_id
WHERE p.data_pedido >= '2024-01-01' 
   OR p.data_pedido IS NULL
ORDER BY p.data_pedido DESC NULLS LAST;

8. Boas Práticas e Armadilhas Comuns

8.1. Quando evitar CROSS JOIN

  • Tabelas com mais de 10.000 registros cada
  • Consultas em produção sem filtros adicionais
  • Ambientes com recursos de memória limitados

8.2. Como interpretar corretamente os resultados do FULL OUTER JOIN

-- Identificando a origem dos dados
SELECT 
    CASE 
        WHEN t1.id IS NOT NULL AND t2.id IS NOT NULL THEN 'Ambas'
        WHEN t1.id IS NOT NULL THEN 'Apenas Tabela 1'
        WHEN t2.id IS NOT NULL THEN 'Apenas Tabela 2'
    END AS origem,
    t1.*,
    t2.*
FROM tabela1 t1
FULL OUTER JOIN tabela2 t2
ON t1.id = t2.id;

8.3. Alternativas e otimizações para grandes volumes de dados

-- Para FULL OUTER JOIN com tabelas grandes, considere:
-- 1. Índices nas colunas de junção
-- 2. Particionamento de tabelas
-- 3. Processamento em lotes

-- Para CROSS JOIN inevitáveis:
-- 1. Limitar com WHERE
-- 2. Usar subconsultas para reduzir dados antes do CROSS JOIN

9. Conclusão

O FULL OUTER JOIN e o CROSS JOIN são ferramentas poderosas no arsenal SQL, cada um com seus casos de uso específicos. Enquanto o FULL OUTER JOIN é essencial para análises completas de dados relacionais, o CROSS JOIN é valioso para gerar combinações e preencher lacunas. O segredo está em entender quando e como aplicá-los, sempre considerando o volume de dados e os requisitos de performance.

Referências