Dominando JOINs, Subqueries e Técnicas de Consulta

Source: Dev.to

1. INNER JOIN

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

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

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

Sintaxe antiga vs. sintaxe ANSI (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;

Formatação de colunas

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

Subqueries são consultas aninhadas dentro de outra consulta, permitindo dividir problemas complexos em partes menores.

Subquery simples

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

Subquery com IN

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

Subquery correlacionada

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

Subquery em FROM (derived table)

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;

Subquery com cálculo adicional

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;

3. Operadores EXISTS / NOT EXISTS

-- Verifica se existe ao menos um funcionário com salário > 10.000
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
);

-- Departamentos que **não** possuem funcionários
SELECT d.department_name
FROM   departments d
WHERE  NOT EXISTS (
    SELECT 1
    FROM   employees e
    WHERE  e.department_id = d.department_id
);

4. Operadores ANY e ALL

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

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

5. Joins avançados com subqueries e CTEs

Exemplo: comparar salário com média do departamento

SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    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;

Exemplo: salário comparado à média da localização

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

6. Common Table Expressions (CTEs)

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;

CTEs encadeados

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;

CTE recursiva – hierarquia de funcionários

WITH RECURSIVE 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;

7. LATERAL (CROSS APPLY)

O operador 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;

Conclusão

Este guia reúne as principais técnicas de consulta avançada no Oracle Database, oferecendo exemplos práticos de:

• INNER JOIN (simples e múltiplos)
• Subqueries (simples, correlacionadas, em FROM)
• Operadores EXISTS, ANY, ALL
• CTEs (simples, encadeados e recursivos)
• LATERAL/CROSS APPLY

Utilize esses padrões para escrever consultas mais legíveis, eficientes e fáceis de manter.

SQL Queries & Best‑Practice Tips

1. Top earners per department

SELECT *
FROM (
    SELECT e.employee_id,
           e.first_name,
           e.salary,
           ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn
    FROM employees e
    WHERE e.department_id = d.department_id
) top_earners
WHERE rn  (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;

3. Employees with manager name and above‑average salary

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

4. Index recommendations

-- Í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);

5. Explain plan for a simple join

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. Sub‑query vs. join performance

EXISTS vs. IN (large data sets)

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

Sub‑query correlacionada vs. join with derived table

-- Menos eficiente (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)
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. Analytic example – monthly sales & top product

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;

8. Recursive hierarchy (employees)

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;

Resumo do Guia

• INNER JOINs – relacionamentos entre tabelas; sintaxe tradicional vs. moderna; múltiplos joins.
• Subqueries – escalares, múltiplas linhas, correlacionadas, em FROM.
• Operadores – EXISTS, ANY, ALL.
• CTEs – simples, encadeados e recursivos.
• LATERAL / CROSS APPLY – subqueries que referenciam a tabela externa.

Utilize esses recursos para otimizar e organizar suas consultas SQL.