Subconsultas para resolver consultas
Uma subconsulta é melhor definida como uma consulta dentro de uma consulta. As subconsultas permitem que você escreva consultas que selecionam linhas de dados para critérios que são realmente desenvolvidos enquanto a consulta está sendo executada em tempo de execução. Mais formalmente, é o uso de uma instrução SELECT dentro de uma das cláusulas de outra instrução SELECT. Na verdade, uma subconsulta pode estar contida em outra subconsulta, que está dentro de outra subconsulta e assim por diante. Uma subconsulta também pode ser aninhada nas instruções INSERT, UPDATE e DELETE. As subconsultas devem ser colocadas entre parênteses.
Uma subconsulta pode ser usada em qualquer lugar onde uma expressão seja permitida, desde que retorne um único valor. Isso significa que uma subconsulta que retorna um único valor também pode ser listada como um objeto em uma listagem de cláusula FROM. Isso é denominado visualização em linha porque quando uma subconsulta é usada como parte de uma cláusula FROM, ela é tratada como uma tabela ou visualização virtual. A subconsulta pode ser colocada na cláusula FROM, cláusula WHERE ou cláusula HAVING da consulta principal.
O Oracle permite um aninhamento máximo de 255 níveis de subconsulta em uma cláusula WHERE. Não há limite para subconsultas aninhadas expressas em uma cláusula FROM. Na prática, o limite de 255 níveis não é realmente um limite, porque é raro encontrar subconsultas aninhadas além de três ou quatro níveis.
Uma instrução SELECT de subconsulta é muito semelhante à instrução SELECT usada para iniciar uma consulta regular ou externa. A sintaxe completa de uma subconsulta é:
( SELECT [DISTINCT] subquery_select_parameter
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY column_name [,column_name ] ...]
[HAVING search_conditions] )Tipos de subconsultas
Single Row Sub Query: Subconsulta que retorna uma saída de linha única. Eles marcam o uso de operadores de comparação de linha única, quando usados em condições WHERE.
Multiple row sub query: Subconsulta que retorna saída de várias linhas. Eles usam vários operadores de comparação de linha como IN, ANY, ALL. Também pode haver subconsultas que retornam várias colunas.
Correlated Sub Query: As subconsultas correlacionadas dependem dos dados fornecidos pela consulta externa. Esse tipo de subconsulta também inclui subconsultas que usam o operador EXISTS para testar a existência de linhas de dados que atendem aos critérios especificados.
Sub Consulta de Linha Única
Uma subconsulta de uma única linha é usada quando os resultados da consulta externa são baseados em um único valor desconhecido. Embora esse tipo de consulta seja formalmente chamado de "linha única", o nome indica que a consulta retorna várias colunas - mas apenas uma linha de resultados. No entanto, uma subconsulta de uma única linha pode retornar apenas uma linha de resultados consistindo em apenas uma coluna para a consulta externa.
Na consulta SELECT abaixo, o SQL interno retorna apenas uma linha, ou seja, o salário mínimo da empresa. Por sua vez, utiliza esse valor para comparar o salário de todos os funcionários e exibe apenas aqueles cujo salário é igual ao salário mínimo.
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);Uma cláusula HAVING é usada quando os resultados do grupo de uma consulta precisam ser restritos com base em alguma condição. Se o resultado de uma subconsulta deve ser comparado com uma função de grupo, você deve aninhar a consulta interna na cláusula HAVING da consulta externa.
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)Subconsulta de várias linhas
Subconsultas de várias linhas são consultas aninhadas que podem retornar mais de uma linha de resultados à consulta pai. Subconsultas de várias linhas são usadas mais comumente nas cláusulas WHERE e HAVING. Uma vez que retorna várias linhas, ele deve ser tratado por operadores de comparação de conjuntos (IN, ALL, ANY). Enquanto o operador IN tem o mesmo significado conforme discutido no capítulo anterior, o operador ANY compara um valor especificado com cada valor retornado pela subconsulta enquanto ALL compara um valor com cada valor retornado por uma subconsulta.
A consulta abaixo mostra o erro quando a subconsulta de uma única linha retorna várias linhas.
SELECT first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one rowUso de operadores de várias linhas
[> ALL] Mais do que o maior valor retornado pela subconsulta
[<ALL] Menor que o menor valor retornado pela subconsulta
[<QUALQUER] Menor que o maior valor retornado pela subconsulta
[> QUALQUER] Mais do que o menor valor retornado pela subconsulta
[= ANY] Igual a qualquer valor retornado pela subconsulta (igual a IN)
Acima do SQL pode ser reescrito usando o operador IN como abaixo.
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 100)Observe na consulta acima, IN corresponde aos ids de departamento retornados da subconsulta, compara-os com os da consulta principal e retorna o nome do funcionário que satisfaz a condição.
Uma junção seria a melhor solução para a consulta acima, mas para fins de ilustração, uma subconsulta foi usada nela.
Sub Consulta Correlacionada
Ao contrário de uma subconsulta regular, em que a consulta externa depende dos valores fornecidos pela consulta interna, uma subconsulta correlacionada é aquela em que a consulta interna depende dos valores fornecidos pela consulta externa. Isso significa que em uma subconsulta correlacionada, a consulta interna é executada repetidamente, uma vez para cada linha que pode ser selecionada pela consulta externa.
As subconsultas correlacionadas podem produzir tabelas de resultados que respondem a questões complexas de gerenciamento.
Considere a consulta SELECT abaixo. Ao contrário das subconsultas consideradas anteriormente, a subconsulta nesta instrução SELECT não pode ser resolvida independentemente da consulta principal. Observe que a consulta externa especifica que as linhas são selecionadas da tabela de funcionários com um nome alternativo de e1. A consulta interna compara a coluna do número do departamento do funcionário (DepartmentNumber) da tabela de funcionários com o alias e2 com a mesma coluna do nome da tabela de alias e1.
SELECT EMPLOYEE_ID, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary)
FROM EMP T
WHERE E.department_id = T.department_id)Subconsulta de várias colunas
Uma subconsulta de várias colunas retorna mais de uma coluna para a consulta externa e pode ser listada na cláusula FROM, WHERE ou HAVING da consulta externa. Por exemplo, a consulta abaixo mostra os detalhes históricos do funcionário para aqueles cujo salário atual está entre 1000 e 2000 e que trabalham no departamento 10 ou 20.
SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000
AND department_id BETWEEN 10 and 20)
ORDER BY first_name;Quando uma subconsulta de várias colunas é usada na cláusula FROM da consulta externa, ela cria uma tabela temporária que pode ser referenciada por outras cláusulas da consulta externa. Essa tabela temporária é mais formalmente chamada de visualização embutida. Os resultados da subconsulta são tratados como qualquer outra tabela na cláusula FROM. Se a tabela temporária contiver dados agrupados, os subconjuntos agrupados serão tratados como linhas separadas de dados em uma tabela. Considere a cláusula FROM na consulta abaixo. A visualização sequencial formada pela subconsulta é a fonte de dados da consulta principal.
SELECT *
FROM (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000);