Tópico 7 – Usando Subqueries

No tópico 7, falarei um pouco sobre subqueries, como e quando usa-las.

1) Quando usar subqueries:

As subqueries podem resolver os seguintes problemas:

  • Criando tabelas populadas: Uma subquery pode ser usada em um comando CREATE TABLE para criar e já popular uma tabela, técnica essa chamada de CTAS ou CREATE TABLE AS SELECT. Sua sintaxe é:
CREATE TABLE MINHA_TABELA_COPIA 
    AS SELECT * FROM MINHA_TABELA_ORIGINAL
  • Manipulação de grandes quantidades de dados: Subqueries podem ser incorporadas em comandos de INSERT e UPDATE para mover grandes quantidades de dados, ou alterar grandes quantidades de dados em um único SQL.
  • Definição dinâmica de VIEWs (Cláusula WITH)
  • Definição dinâmica de expressões com subqueries escalares (subqueries que retornam apenas um valor)

2) Como usar subqueries

Existem alguns tipos de subqueries, cada uma com suas vantagens/desvantagens. Abaixo é possível ver os tipos de subqueries bem como alguns exemplos para ajudar a entende-las.

NOTA.01 – Caso a subquery retorne 0 linhas, então o valor retornado pela subquery é NULL

NOTA.02 – É possível ter subqueries no WHERE, HAVING e GROUP BY.

2.1) Single-row subqueries

Subqueries que retornam apenas uma linha, e uma ou varias colunas Ex:

-- retorna o id e o nome do departamento do empregado chamado 
-- 'stalman jones'
-- caso a subquery retorne mais de uma linha, o seguinte erro 
-- será lançado:
-- ORA-01427: single-row subquery returns more than one row
SELECT ID_DEPARTAMENTO, NOME_dEPARTAMENTO 
 FROM CERTIFICACAO.TAB_DEPARTAMENTOS 
 WHERE ID_GERENTE = (SELECT ID_EMP 
 FROM CERTIFICACAO.TAB_EMPREGADOS WHERE NOME_EMP = 'STALMAN JONES')

2.2) Multiple-row subqueries

Subqueries que retornam 0, uma ou varias linhas. Ex:

-- retorna algumas informações sobre os empregados que pertencem
-- ao setor 'tecnologia da informação'
-- reparem no uso do n, quando se esperar que a subquery retorne 
-- mais de uma linha.
SELECT ID_EMP,
 NOME_EMP,
 SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE ID_DEPARTAMENTO IN (SELECT ID_DEPARTAMENTO 
 FROM CERTIFICACAO.TAB_DEPARTAMENTOS 
 WHERE NOME_DEPARTAMENTO = 'TECNOLOGIA DA INFORMAÇÃO')

2.3) Multiple-column subqueries

Retorna mais de uma coluna para a query pai. Pode retornar uma ou várias linhas. Ex:

-- retorna informações sobre o gerente da área 
-- de 'tecnologia da informação'
-- reparem no uso de 2 colunas antes do operador in. essas 
-- 2 colunas devem ser equivalentes as colunas usadas na subquery

SELECT ID_EMP,
 NOME_EMP,
 SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS
 WHERE (ID_DEPARTAMENTO, ID_EMP) 
IN (SELECT ID_DEPARTAMENTO, 
 ID_GERENTE 
 FROM CERTIFICACAO.TAB_DEPARTAMENTOS 
 WHERE NOME_DEPARTAMENTO = 'TECNOLOGIA DA INFORMAÇÃO')

2.4) Correlated Subqueries:

Especifica colunas que pertencem a tabelas que são referenciadas pela tabela pai. Ex:

-- mostra informações dos gerentes de cada departamento

SELECT TE.ID_EMP,
 TE.NOME_EMP,
 TE.SALARIO,
 TE.ID_DEPARTAMENTO
 FROM CERTIFICACAO.TAB_EMPREGADOS TE
 WHERE TE.ID_EMP IN (SELECT TD.ID_GERENTE 
 FROM CERTIFICACAO.TAB_DEPARTAMENTOS TD 
 WHERE TD.ID_DEPARTAMENTO = TE.ID_DEPARTAMENTO)

-- também é possível ser usada em updates
-- abaixo se esta atualizando os salários dos gerentes em 10%

UPDATE CERTIFICACAO.TAB_EMPREGADOS TE
 SET SALARIO = (SALARIO * 1.10)
 WHERE ID_EMP IN 
   (SELECT TD.ID_GERENTE FROM CERTIFICACAO.TAB_DEPARTAMENTOS TD 
     WHERE TD.ID_DEPARTAMENTO = TE.ID_DEPARTAMENTO)

3) Usando o EXISTS

O EXISTS testa a existência de uma linha em uma subquery. Caso nenhuma linha seja encontrada, retorna FALSE. Caso a subquery retorne mais de uma linha, se essa linha “bater” com a linha da query-pai o comando é encerrado, não sendo necessário todas as linhas do EXISTS serem validadas. Mais performático do que o operador IN. Ex:

-- checa a existencia do gerente do setor 1

SELECT TE.NOME_EMP,
 TE.SALARIO
 FROM CERTIFICACAO.TAB_EMPREGADOS TE
 WHERE EXISTS (SELECT 1
 FROM CERTIFICACAO.TAB_DEPARTAMENTOS TD
 WHERE TD.ID_GERENTE = TE.ID_EMP
 AND TD.ID_DEPARTAMENTO = 1)

4) Usando o WITH

O WITH determina nomes para subqueries (blocos de subqueries). Essa subquery, que foi nomeada é tratada como uma view dinâmica. Isso facilita a leitura e manutenção caso se tenha que trabalhar com muitas subqueries dentro de uma query Ex:

-- mostra os funcionários do setor de 'tecnologia da informação'

WITH SETOR_TI AS (
 SELECT ID_DEPARTAMENTO FROM CERTIFICACAO.TAB_DEPARTAMENTOS WHERE NOME_DEPARTAMENTO = 'TECNOLOGIA DA INFORMAÇÃO'
)
SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS 
 WHERE ID_DEPARTAMENTO = (SELECT ID_DEPARTAMENTO FROM SETOR_TI)

Bem… é isso por enquanto pessoal. Até a próxima.

Keep Querying