Técnicas Avançadas de Subconsultas no Oracle

Subconsultsa Básicas

Uma subconsulta é uma instrução SELECT aninhada dentro de outra instrução SQL.

SELECT colunas_selecionadas
FROM tabela
WHERE expressao operador (SELECT colunas_selecionadas
                          FROM tabela);

A subconsulta é executada antes da consulta principal, e seu resultado é utilizado por ela.

Exemplo:

SELECT primeiro_nome
FROM rh.empregados
WHERE salario > (SELECT salario
                 FROM rh.empregados
                 WHERE id_empregado = 155);

PRIMEIRO_NOME
Silva
Souza
Oliveira
Santos
Pereira
Costa
Rodrigues
Ferreira
Almeida
Nascimento

10 linhas selecionadas.

Subconsultas Multi-Coluna

Uma subconsulta multi-coluna retorna mais de uma coluna. A consulta principal compara com as múltiplas colunas retornadas.

A comparação pode ser:

Pareada: Os pares de colunas são comparados juntos.
Não Pareada: Cada coluna é comparada individualmente.

Comparação Pareada

SELECT id_empregado, id_gerente, id_departamento
FROM rh.empregados
WHERE (id_gerente, id_departamento) IN (SELECT id_gerente, id_departamento
                                        FROM rh.empregados
                                        WHERE id_empregado IN (180, 176))
AND id_empregado NOT IN (180, 176);

ID_EMPREGADO	ID_GERENTE	ID_DEPARTAMENTO
177	150	80
178	150	80
179	150	80
181	150	80

4 linhas selecionadas.

Comparação Não Pareada

SELECT id_empregado, id_gerente, id_departamento
FROM rh.empregados
WHERE id_gerente IN (SELECT id_gerente
                     FROM rh.empregados
                     WHERE id_empregado IN (176, 143))
AND id_departamento IN (SELECT id_departamento
                        FROM rh.empregados
                        WHERE id_empregado IN (176, 143))
AND id_empregado NOT IN (176, 143);

ID_EMPREGADO	ID_GERENTE	ID_DEPARTAMENTO
144	124	50
145	124	50
146	124	50
198	124	50
199	124	50
200	124	50
177	150	80
178	150	80
179	150	80
181	150	80

10 linhas selecionadas.

Subconsultas na Cláusula FROM

Uma subconsulta pode ser utilizada como uma tabela derivada (inline view) na cláusula FROM.

SELECT emp.primeiro_nome, emp.salario, emp.id_departamento, media_sal.dept_media
FROM rh.empregados emp
JOIN (SELECT id_departamento, AVG(salario) AS dept_media
      FROM rh.empregados
      GROUP BY id_departamento) media_sal
ON emp.id_departamento = media_sal.id_departamento
WHERE emp.salario > media_sal.dept_media;

Esta consutla retorna empregados cujo salário é maior que a média salarial de seus respectivos departamentos.

Expressões de Subconsulta de Coluna Única

Uma subconsulta de coluna única é uma que retorna exatamente uma coluna e uma linha.

No Oracle, essas subconsultas podem ser usadas em diversas cláusulas como SELECT, WHERE, ORDER BY, e dentro de expressões como CASE e DECODE.

Uso na Expressão CASE

-- Primeiro, obtenha o departamento para a localização
SELECT id_departamento
FROM rh.departamentos
WHERE id_local = 1500;

-- Resultado: id_departamento = 50

SELECT id_empregado, primeiro_nome,
       CASE WHEN id_departamento = (SELECT id_departamento
                                    FROM rh.departamentos
                                    WHERE id_local = 1500)
            THEN 'São Paulo' 
            ELSE 'Outra Localidade' 
       END AS localizacao
FROM rh.empregados;

Atribui 'São Paulo' para empregados do departamento 50 e 'Outra Localidade' para os demais.

Uso na Cláusula ORDER BY

SELECT e.id_empregado, e.primeiro_nome
FROM rh.empregados e
ORDER BY (SELECT d.nome_departamento
          FROM rh.departamentos d
          WHERE e.id_departamento = d.id_departamento);

Ordena os empregados pelo nome do seu departamento.

Subconsultas Correlacionadas

Uma subconsulta correlacionada é executada uma vez para cada linha candidata da consulta externa. Ela referancia colunas da consulta externa.

SELECT colunas
FROM tabela1 externa
WHERE expressao operador 
    (SELECT colunas
     FROM tabela2
     WHERE condicao = externa.coluna);

Exemplo 1: Encontrar empregados com salário acima da média do seu departamento.

SELECT nome, salario, id_departamento
FROM rh.empregados externo
WHERE salario > (SELECT AVG(salario)
                 FROM rh.empregados
                 WHERE id_departamento = externo.id_departamento);

Exemplo 2: Encontrar empregados com mais de um histórico de emprego.

SELECT e.id_empregado, e.primeiro_nome, e.cargo_id
FROM rh.empregados e
WHERE 2 <= (SELECT COUNT(*)
            FROM rh.historico_emprego
            WHERE id_empregado = e.id_empregado);

ID_EMPREGADO	PRIMEIRO_NOME	CARGO_ID
101	Costa	DIR_VP
176	Lima	REP_VND
200	Santos	AST_ADM

3 linhas selecionadas.

Operador EXISTS

O operador EXISTS é usado para verificar se a subconsulta retorna alguma linha. Retorna TRUE se a subconsulta retornar pelo menos uma linha, caso contrário FALSE.

Exemplo: Selecionar gerentes (empregados que têm subordinados).

SELECT id_empregado, primeiro_nome, cargo_id, id_departamento
FROM rh.empregados externo
WHERE EXISTS (SELECT 1
              FROM rh.empregados
              WHERE id_gerente = externo.id_empregado);

ID_EMPREGADO	PRIMEIRO_NOME	CARGO_ID	ID_DEPARTAMENTO
100	Silva	PRES_ADM	90
101	Costa	DIR_VP	90
108	Ferreira	GER_FIN	100
114	Almeida	GER_COMPR	30
120	Rodrigues	GER_EST	50
121	Pereira	GER_EST	50
145	Souza	GER_VND	80
146	Oliveira	GER_VND	80
201	Nascimento	GER_MKT	20

9 linhas selecionadas.

Operador NOT EXISTS

O operador NOT EXISTS funciona de maneira oposta. Retorna TRUE se a subconsulta não retornar nenhuma linha.

Exemplo: Selecionar departamentos que não possuem empregados.

SELECT id_departamento, nome_departamento
FROM rh.departamentos d
WHERE NOT EXISTS (SELECT 1
                  FROM rh.empregados
                  WHERE id_departamento = d.id_departamento);

Atualização e Exclusão Correlacionadas

Subconsultas correlacionadas podem ser usadas em instruções UPDATE e DELETE para modificar ou remover linhas baseadas em dados de outra tabela.

Atualização Correlacionada

-- Adicionar uma coluna à tabela de empregados
ALTER TABLE empregados ADD (nome_departamento VARCHAR2(30));

-- Atualizar a nova coluna usando uma subconsulta correlacionada
UPDATE empregados emp
SET nome_departamento = (SELECT nome_departamento
                         FROM departamentos dept
                         WHERE emp.id_departamento = dept.id_departamento);

Exclusão Correlacionada

-- Excluir empregados que estão no histórico de emprego (exemplo hipotético)
DELETE FROM empregados emp
WHERE EXISTS (SELECT 1
              FROM historico_emprego hist
              WHERE hist.id_empregado = emp.id_empregado);

Cláusula WITH (Subconsulta Fatorada Comum)

A cláusula WITH permite definir subconsultas com nome que podem ser referenciadas múltiplas vezes na consulta principal. Isso melhora a legibilidade e, potencialmente, o desempenho, pois a subconsulta é materializada uma vez.

WITH
custo_dept AS (
    SELECT dept.nome_departamento, SUM(emp.salario) AS total_dept
    FROM empregados emp
    JOIN departamentos dept ON emp.id_departamento = dept.id_departamento
    GROUP BY dept.nome_departamento
),
media_dept AS (
    SELECT AVG(total_dept) AS media_total_dept
    FROM custo_dept
)
SELECT cd.nome_departamento, cd.total_dept
FROM custo_dept cd, media_dept md
WHERE cd.total_dept > md.media_total_dept
ORDER BY cd.nome_departamento;

Esta consulta retorna os departamentos cujo custo salarial total é superior à média dos custos de todos os departamentos.

NOME_DEPARTAMENTO	TOTAL_DEPT
Vendas	304500
Envios	156400

2 linhas selecionadas.

Tags: Oracle SQL Subconsultas Subconsultas Correlacionadas EXISTS WITH

Publicado em 7-16 05:44

Doido Dev