Dominando JOINs, Subqueries e Técnicas de Consulta

O Oracle Database oferece recursos poderosos para consultar e relacionar dados. Este guia aborda em profundidade INNER JOINs, subqueries e técnicas avançadas de consulta SQL no Oracle.

1. INNER JOIN

1.1 Conceito Fundamental

O INNER JOIN retorna apenas as linhas que possuem correspondência em ambas as tabelas envolvidas na junção. É o tipo de JOIN mais comum e forma a base para relacionamentos entre tabelas.

1.2 Sintaxe Básica

SELECT colunas
FROM tabela1
INNER JOIN tabela2 ON tabela1.coluna = tabela2.coluna;

1.3 Exemplos Práticos

Exemplo 1: Relacionando Funcionários e Departamentos

SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Exemplo 2: INNER JOIN com Múltiplas Condições

SELECT 
    o.order_id,
    c.customer_name,
    p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATE '2024-01-01';

1.4 INNER JOIN vs Sintaxe Antiga (Oracle)

O Oracle ainda suporta a sintaxe antiga de JOIN, mas a sintaxe ANSI (INNER JOIN) é recomendada:

-- Sintaxe antiga (não recomendada)
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Sintaxe moderna (recomendada)
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

1.5 INNER JOIN com Aliases e Funções

SELECT 
    UPPER(e.last_name) AS sobrenome,
    d.department_name AS departamento,
    TO_CHAR(e.salary, 'L99G999D99') AS salario_formatado
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000
ORDER BY e.salary DESC;

2. Subqueries (Subconsultas)

2.1 O Que São Subqueries?

Subqueries são consultas SQL aninhadas dentro de outra consulta. Elas permitem resolver problemas complexos dividindo-os em partes menores e mais gerenciáveis.

2.2 Tipos de Subqueries

2.2.1 Subquery Escalar (Retorna um Único Valor)

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2.2.2 Subquery de Múltiplas Linhas

SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

2.2.3 Subquery Correlacionada

Uma subquery correlacionada referencia colunas da query externa:

SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

2.3 Subqueries na Cláusula FROM (Inline Views)

SELECT dept_avg.department_id, dept_avg.media_salarial, d.department_name
FROM (
    SELECT department_id, AVG(salary) AS media_salarial
    FROM employees
    GROUP BY department_id
) dept_avg
INNER JOIN departments d ON dept_avg.department_id = d.department_id
WHERE dept_avg.media_salarial > 8000;

2.4 Subqueries na Cláusula SELECT

SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    (SELECT AVG(salary) FROM employees) AS media_geral,
    e.salary - (SELECT AVG(salary) FROM employees) AS diferenca_media
FROM employees e;

2.5 Operadores com Subqueries

EXISTS

Verifica se a subquery retorna algum resultado:

SELECT d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary > 10000
);

NOT EXISTS

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

ANY / SOME

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 50
);

ALL

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 50
);

3. Combinando INNER JOIN e Subqueries

3.1 Exemplo Avançado

SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS nome_completo,
    d.department_name,
    e.salary,
    dept_stats.media_dept,
    dept_stats.max_dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN (
    SELECT 
        department_id,
        AVG(salary) AS media_dept,
        MAX(salary) AS max_dept
    FROM employees
    GROUP BY department_id
) dept_stats ON e.department_id = dept_stats.department_id
WHERE e.salary > dept_stats.media_dept * 1.2;

3.2 Subquery com JOIN Múltiplo

SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    INNER JOIN departments d2 ON e2.department_id = d2.department_id
    WHERE d2.location_id = l.location_id
);

4. Common Table Expressions (WITH Clause)

As CTEs oferecem uma alternativa mais legível às subqueries complexas:

4.1 CTE Simples

WITH dept_salaries AS (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary,
        COUNT(*) AS num_employees
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.avg_salary,
    ds.num_employees
FROM dept_salaries ds
INNER JOIN departments d ON ds.department_id = d.department_id
WHERE ds.avg_salary > 7000;

4.2 Múltiplas CTEs

WITH 
high_earners AS (
    SELECT employee_id, first_name, salary, department_id
    FROM employees
    WHERE salary > 10000
),
dept_info AS (
    SELECT department_id, department_name, location_id
    FROM departments
)
SELECT 
    he.first_name,
    he.salary,
    di.department_name
FROM high_earners he
INNER JOIN dept_info di ON he.department_id = di.department_id
ORDER BY he.salary DESC;

4.3 CTE Recursiva

WITH employee_hierarchy (employee_id, first_name, manager_id, level_num) AS (
    -- Anchor member
    SELECT employee_id, first_name, manager_id, 1 AS level_num
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.employee_id, e.first_name, e.manager_id, eh.level_num + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    LPAD(' ', (level_num - 1) * 2) || first_name AS hierarchy,
    level_num
FROM employee_hierarchy
ORDER BY level_num, first_name;

5. Técnicas Avançadas

5.1 LATERAL JOIN (Oracle 12c+)

O LATERAL permite que a subquery à direita referencie colunas da tabela à esquerda:

SELECT 
    d.department_name,
    top_earners.*
FROM departments d
CROSS APPLY (
    SELECT employee_id, first_name, salary
    FROM employees e
    WHERE e.department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) top_earners;

5.2 Subquery com CASE

SELECT 
    employee_id,
    first_name,
    salary,
    CASE 
        WHEN salary > (SELECT AVG(salary) FROM employees) 
        THEN 'Acima da Média'
        WHEN salary = (SELECT AVG(salary) FROM employees) 
        THEN 'Na Média'
        ELSE 'Abaixo da Média'
    END AS classificacao_salarial
FROM employees;

5.3 Self-Join com Subquery

SELECT 
    e1.employee_id AS funcionario_id,
    e1.first_name AS funcionario_nome,
    e2.first_name AS gerente_nome,
    e1.salary
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e1.salary > (
    SELECT AVG(salary)
    FROM employees e3
    WHERE e3.manager_id = e1.manager_id
);

6. Otimização e Boas Práticas

6.1 Índices

Crie índices nas colunas usadas em JOINs e WHERE:

CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_loc ON departments(location_id);

6.2 Uso do EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

6.3 Evite Subqueries Desnecessárias

Ruim:

SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments);

Melhor:

SELECT e.* FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

6.4 Use EXISTS ao Invés de IN Quando Apropriado

Para grandes conjuntos de dados, EXISTS pode ser mais eficiente:

-- Menos eficiente
SELECT * FROM employees e
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

-- Mais eficiente
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.location_id = 1700
);

6.5 Evite Subqueries Correlacionadas Quando Possível

Menos eficiente (subquery correlacionada):

SELECT e.employee_id, e.salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id) AS avg_dept_salary
FROM employees e;

Mais eficiente (JOIN com subquery):

SELECT e.employee_id, e.salary, dept_avg.avg_salary
FROM employees e
INNER JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id;

7. Exemplos Práticos Completos

7.1 Análise de Vendas

WITH monthly_sales AS (
    SELECT 
        TRUNC(order_date, 'MM') AS month,
        SUM(total_amount) AS total_sales,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM orders
    WHERE order_date >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY TRUNC(order_date, 'MM')
),
product_performance AS (
    SELECT 
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    INNER JOIN order_items oi ON p.product_id = oi.product_id
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY p.product_id, p.product_name
)
SELECT 
    ms.month,
    ms.total_sales,
    ms.unique_customers,
    pp.product_name AS top_product,
    pp.revenue AS top_product_revenue
FROM monthly_sales ms
CROSS JOIN LATERAL (
    SELECT product_name, revenue
    FROM product_performance
    ORDER BY revenue DESC
    FETCH FIRST 1 ROW ONLY
) pp
ORDER BY ms.month DESC;

7.2 Relatório de Hierarquia com Métricas

WITH RECURSIVE emp_hierarchy AS (
    SELECT 
        employee_id,
        first_name,
        manager_id,
        salary,
        1 AS level_depth,
        CAST(first_name AS VARCHAR2(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT 
        e.employee_id,
        e.first_name,
        e.manager_id,
        e.salary,
        eh.level_depth + 1,
        eh.path || ' > ' || e.first_name
    FROM employees e
    INNER JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    eh.path,
    eh.salary,
    (SELECT AVG(salary) FROM employees) AS avg_company_salary,
    (SELECT AVG(salary) FROM employees e WHERE e.manager_id = eh.manager_id) AS avg_peer_salary,
    CASE 
        WHEN eh.salary > (SELECT AVG(salary) FROM employees e WHERE e.manager_id = eh.manager_id)
        THEN 'Acima dos Pares'
        ELSE 'Abaixo dos Pares'
    END AS comparacao
FROM emp_hierarchy eh
ORDER BY eh.level_depth, eh.first_name;

8. Resumo e Conclusão

Este guia cobriu extensivamente:

• INNER JOINs: Relacionamento entre tabelas, sintaxes antigas vs modernas, múltiplos JOINs
• Subqueries: Escalares, múltiplas linhas, correlacionadas, inline views
• Operadores: EXISTS, NOT EXISTS, IN, ANY, ALL
• CTEs: Expressões de tabela comuns, incluindo recursivas
• Técnicas Avançadas: LATERAL JOIN, self-joins, combinações complexas
• Otimização: Índices, EXPLAIN PLAN, boas práticas de performance

O domínio dessas técnicas permite escrever consultas SQL complexas, eficientes e manuteníveis no Oracle Database. Pratique regularmente e sempre considere a performance ao escolher entre diferentes abordagens.