O que devemos conhecer sobre SQL/Oracle Parte 9: Recuperando dados utilizando subconsultas

Definindo subconsultas

Como visto anteriormente uma consulta é um comando SELECT, logo uma subconsulta é um comando SELECT dentro de outro comando, que pode ser outro SELECT, INSERT, UPDATE, DELETE, CREATE TABLE ou CREATE VIEW:

• Subconsulta é um SELECT dentro de outro comando SQL;
• O outro comando SQL é chamado de pai. O nível externo é chamado de top-level;
• O comando SQL top-level contendo a subconsulta pode ser um SELECT, INSERT, UPDATE, DELETE, CREATE TABLE ou CREATE VIEW;
• Subconsultas podem estar aninhadas em outras subconsultas;
• Algumas subconsultas podem funcionar de forma autônoma. Outras funcionam de modo correlacionado, sempre que possuem referencia a suas consulta pai;

Descrevendo os tipos de problemas que subconsultas podem resolver

As subconsultas podem ser utilizadas para resolver uma série de problemas, sendo eles:

Consultas complexas: subconsultas podem encontrar respostas para perguntas e então usar essas respostas para responder novas perguntas.

Criando tabelas populadas: uma subconsulta incorporada a um comando CREATE TABLE pode rapidamente criar uma tabela com dados.

Manipulação de grandes massas de dados: subconsultas incorporadas aos comandos INSERT ou UPDATE podem mover grandes massas de dados, inserindo ou atualizando muitos registros de uma fonte de dados para outra.

Criando visões nomeadas: uma subconsulta poder ser usada para criar uma visão (VIEW).

Definição dinâmica de uma visão: uma subconsulta quando utilizada na cláusula FROM, simula uma tabela. Essa utilização é conhecida como INLINE VIEW.

Definição de expressões dinâmicas com subconsultas escalares: uma subconsulta que retorne um único valor (um registro de uma coluna), pode ser utilizado na maioria dos locais de um comando SQL.

Resumindo:

• Uma subconsulta pode prover dados para ajudar a consulta pai a complementar a clausula WHERE;
• Subconsultas podem ajudar a compor comandos de muitos passos em um único, reduzindo o que seriam vários comandos consecutivos em um único comando;
• Subconsultas em CREATE TABLE ou INSERT ou UPDATE podem recuperar dados do banco inserindo em outros objetos de uma forma mais rápida;
• Subconsultas podem nomear consultas para uma possível referencia;

Lista dos tipos de subconsultas

Existem vários tipos diferentes de subconsultas:

Subconsultas single-row: retornam um único registro como resultado, podendo possuir mais de uma coluna.

Subconsultas multiple-row: Podem retornar zero, um ou mais registros, ele não obriga o retorno de vários registros, mas pode retornar, logo a consulta pai deve estar estruturada para receber vários registros. Deve-se tomar cuidado para não utilizar esse tipo de consulta em condições WHERE onde a comparação é uma igualdade, por exemplo, ou mesmo usá-la como uma coluna.

Subconsultas multiple-column: retornam mais de uma coluna, podendo possuir um ou mais registros.

Subconsultas correlacionadas: esse tipo de subconsulta pode especificar colunas que não pertencem a ela, ou seja, colunas da consulta pai. A correlação realiza a análise registro a registro em conjunto com a consulta pai, acessando os dados e fazendo referencia a esses dados em suas próprias expressões. Podem ser utilizadas em comandos SELECT, UPDATE E DELETE. Elas podem ser single-row, multiple-row ou multiple-column.

Subconsultas escalares: consistem no retorno de um registro e uma coluna.

Resumindo:

• Subconsulta single-row retorna um único registro;
• Subconsulta multi-row pode retornar mais de um registro;
• Subconsulta multiple-column retorna duas ou mais colunas para a consulta pai, e essa deve testar todas as colunas de uma única vez;
• Subconsultas correlacionadas utilizam dados da consulta pai para determinar o próprio resultado;
• Subconsultas escalares sempre retornam um valor, representado por uma coluna, sempre;
• Subconsulta multiple-column pode ser do tipo single-row ou multiple-row;
• Subconsulta correlacionada pode ser single-row, multiple-row ou multiple-column;

Escrevendo subconsultas single-row e multiple-row

Como visto anteriormente subconsultas single-row recuperam um único registro, então vejamos a seguinte situação, nós gostaríamos de verificar os acessos ao sistema realizados por um determinado usuário, para alcançar nosso objetivo poderíamos fazer isso da forma convencional, com duas consultas:

Primeiro procuraríamos pelo código do funcionário, que no caso será um

SELECT F.COD FROM FUNCIONARIOS F WHERE F.NOME = ‘JOÃO SILVA’;

Em seguida pelos acessos

SELECT L.SISTEMA FROM LOGS L WHERE L.COD = 1;

Agora se usarmos o conceito de subconsulta single-row podemos realizar essa tarefa com um único comando

SELECT L.SISTEMA FROM LOGS L WHERE L.COD = (SELECT F.COD FROM FUNCIONARIOS F WHERE F.NOME = ‘JOÃO SILVA’);

É muito importante ressaltar que no caso acima se existir mais de um usuário com o nome JOÃO SILVA teremos um erro. As condicionais de comparação que podem ser utilizadas nesse tipo de subconsulta são igual(=), diferente(<>, !=, ^=), maior que(>), maior ou igual(>=), menor que(<), menor ou igual(<=), LIKE e IN.

Já as subconsultas multiple-row retornam vários registros, para exemplificar tomemos o exemplo anterior, só que agora vamos considerar o caso de exceção, onde existem mais de um usuário com o mesmo nome, nessa situação precisaríamos apenas mudar o operador de comparação para um dos tipos permitidos, que são o IN, NOT, ANY ou SOME, para nosso caso usaremos o IN:

SELECT L.SISTEMA FROM LOGS L WHERE L.COD IN (SELECT F.COD FROM FUNCIONARIOS F WHERE F.NOME = ‘JOÃO SILVA’);

Resumindo:

• Os resultados da subconsulta single-row podem ser comparados utilizando um operador escalar, como o sinal de igual ou maior, por exemplo;
• Nomes de colunas não precisam ser iguais, mas devem possuir o mesmo tipo de dados;
• Subconsulta multiple-row são comparadas de maneira diferente, utilizando os operadores IN, ANY ou ALL, em conjunto com os operadores diferente ou menor, por exemplo;

Escrevendo subconsultas multiple-column

Até agora nossos exemplos foram apenas com consultas que retornam uma única coluna, agora vejamos o seguinte exemplo, considerando que possuímos em nossa tabela de funcionários colunas distintas para armazenar nome e sobrenome, e que os gerentes são armazenados em uma tabela distinta com o mesmo conceito destacado acima, devemos realizar uma consulta que nos mostre todos os funcionários que possuam nomes idênticos aos nomes dos gerentes, perceba que agora temos um caso onde precisaremos comparar mais de uma coluna, logo:

SELECT F.NOME, F.SOBRENOME FROM FUNCIONARIOS F WHERE (F.NOME, F.SOBRENOME) IN (SELECT G.NOME, G.SOBRENOME FROM GERENTES G);

Resumindo:

• Subconsultas multiple-column retornam varias colunas de uma vez;
• A consulta pai deve comparar todas as colunas de uma vez, os tipos de dados de cada expressão de comparação devem ser iguais;
• Subconsultas multiple-column podem retornar um ou mais registros;

Utilizando subconsultas escalares

As subconsultas escalares sempre retornam uma única coluna em um único registro, desse modo podem ser utilizadas em muitos locais de um comando SQL, como por exemplo, na lista de colunas de uma consulta:

SELECT F.NOME, (SELECT D.TOTAL FROM DESCONTOS D WHERE D.COD = 1) DESCONTO FROM FUNCIONARIOS F;

Na consulta acima teremos o nome de todos os funcionários seguido do total estipulado de desconto cujo código seja um. Esse tipo de subconsulta deve sempre estar entre parênteses e não pode ser utilizada em restrições CHECK, cláusulas GROUP BY, cláusulas HAVING, valor DEFAULT de uma coluna, em cláusulas RETURNING de comandos DML, na condição WHEN do comando CASE, e nas cláusulas START WITH e no CONNECT BY.

Resumindo:

• Subconsultas escalares retornam dados na forma de um único valor (uma coluna em um registro);
• Subconsultas escalares podem ser usadas em qualquer lugar onde uma expressão escalar puder ser usada;

Resolvendo problemas com consultas correlacionadas

Como visto anteriormente subconsultas correlacionadas se integram com a consulta pai, fazendo referencia a elementos da consulta pai, de modo que ela não poderia ser executada sozinha. Como demonstração podemos pensar no ultimo exemplo, lá indicamos que apenas o desconto de código um seria mostrado, agora vamos considerar que cada funcionário possui um código de desconto, dessa forma queremos saber os descontos de cada funcionário:

SELECT F.NOME, (SELECT D.TOTAL FROM DESCONTOS D WHERE D.COD = F.DESCONTO) DESCONTO FROM FUNCIONARIOS F;

Desse modo para cada registro da tabela funcionário o código de desconto dessa tabela será usado na subconsulta para gerar um resultado único. Vale ressaltar que diferente do exemplo anterior se pegarmos apenas a subconsulta e tentarmos executa-la teremos um erro, já que estamos usando o código de desconto do funcionário.

Resumindo:

• Subconsultas correlacionadas utilizam dados da consulta pai para determinar os dados que serão retornados;
• Subconsultas correlacionadas podem apresentar problemas de performance, mas oferecem soluções que não podem ser realizadas de outra forma;

Atualizando e removendo registros com subconsultas correlacionadas

As consultas correlacionadas podem ser utilizadas nas cláusulas SET e WHERE do comando UPDATE, vejamos o seguinte exemplo, desejamos atualizar uma tabela que totaliza funcionários pelo valor do desconto:

UPDATE HISTORICO_DESCONTO H SET TOTAL = (SELECT COUNT(*) FROM FUNCIONARIOS F WHERE F.DESCONTO = H.DESCONTO) WHERE EXISTS (SELECT * FROM FUNCIONARIOS F WHERE F.DESCONTO = H.DESCONTO);

Todos os históricos de descontos relacionados a algum funcionário serão atualizados com o total de funcionários que possuem esse tipo de desconto.

Para o caso do comando DELETE podemos usar essa subconsulta apenas na cláusula WHERE, da mesma maneira que usamos no exemplo acima.

Resumindo:

• Os comandos UPDATE e DELETE podem usar subconsultas correlacionadas;
• O UPDATE pode usar subconsultas nas clausulas SET e WHERE, o DELETE na clausula WHERE;

Utilizando os operadores EXISTS e NOT EXISTS

No ultimo exemplo utilizamos o EXISTS na condição WHERE, ele não realiza necessariamente uma comparação, e sim uma verificação da existência de registros na subconsulta: 

• O EXISTS pode ser usado pela consulta pai para verificar se uma subconsulta retorna valor;
• O NOT EXISTS funciona de forma contraria;

Utilizando a clausula WITH

Essa cláusula é utilizada para nomear um bloco de subconsulta, sendo considerando uma cláusula do comando SELECT, vejamos um exemplo:

WITH W_GERENTES AS (SELECT G.NOME, G.SALARIO FROM GERENTES G), W_DESCONTOS AS (SELECT MAX(D.VALOR) MAIOR FROM DESCONTOS D) SELECT NOME FROM W_GERENTES WHERE SALARIO = (SELECT MAIOR FROM W_DESCONTOS);

Perceba que na consulta acima temos duas subconsultas, sendo uma retornando todos os nomes e salários dos gerentes e outra retornando o maior valor de desconto, em seguida temos a realização da consulta de todos os gerentes cujo salário seja igual ao maior valor de desconto.

Resumindo:

• A cláusula WITH pode nomear dinamicamente uma subconsulta de modo que o comando SELECT referencie essa subconsulta por nome, tratando-a como uma tabela dinâmica de tempo real;
• As subconsultas definidas pela clausula WITH não são persistidas na base de dados, só podem ser usadas durante a execução do comando;