SQL ORACLE SUBCONSULTAS

Prévia do material em texto

*
Marcelo Hely
*
SQL ORACLE - AULA 6
SUBCONSULTAS
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Sintaxe da Subconsulta
Uma subconsulta é uma instrução SELECT que é incorporada a uma cláusula de outra instrução SELECT.
Uma subconsulta (consulta interna) é executada uma vez antes da consulta principal.
O resultado da subconsulta é usado pela consulta principal (consulta externa)
Pode ser chamada de SELECT aninhada, sub-SELECT ou SELECT interna.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Sintaxe da Subconsulta
Poderão ser úteis quando você precisar selecionar linhas de uma tabela com uma condição que dependa dos dados da própria tabela.
Pode-se colocar a subconsulta com cláusulas incluindo: WHERE, HAVING e FROM
As condições de comparação dividem-se em duas classes:
Operadores de uma única linha (>,=, >=,<,<>,<=)
Operadores de várias linhas (IN, ANY, ALL)
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Utilizando uma Subconsulta
SELECT ename
FROM emp
where sal > (SELECT sal
	 from emp
	 where ename = 'ADAMS')
No exemplo, a consulta interna determina o salário de ADAMS. A consulta externa obtém o resultado da consulta interna e o utiliza para exibir todos os funcionários que recebam mais que essa quantia.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Diretrizes para o uso de subconsulta
Coloque as subconsultas entre parênteses.
Coloque as subconsultas no lado direito da condição de comparação.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Tipos de Subconsultas
Subconsultas de um única linha 
Retorna somente uma linha.
Utiliza-se operadores de comparação de uma única linha. (=, >,<, etc..)
SQL> SELECT ename, job
 2 from emp
 3 where job = (SELECT job
 4 	FROM emp
 5 	WHERE empno = 7499);
O exemplo exibe os funcionários cujo cargo seja o mesmo do funcionário 7499.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Executando Subconsultas de uma única linha
1 SELECT ename, job, sal
2 FROM emp
3 WHERE job = (SELECT job
4 		FROM emp
5 		where empno = 7499)
6 AND sal > (SELECT sal
7 	 FROM emp
8* 	 WHERE empno = 7876)
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Executando Subconsultas de uma única linha
O exemplo mostra os funcionários cujo cargo seja o mesmo que do funcionário 7499 e cujo salário seja maior que do funcionário 7876.
O exemplo consiste em três blocos de consulta: a consulta externa e duas consultas internas. Os blocos de consulta interna são executados primeiro, gerando os resultados de consulta SALESMAN e 1100. Essa instrução é chamada de subconsulta de uma única linha
O bloco de consulta externa é então processado e usa os valores retornados pelas consultas internas para completar suas condições de pesquisa.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Usando Funções de grupo em uma Subconsulta
1 SELECT ename, job, sal
2 FROM emp
3 WHERE sal = ( SELECT MIN(sal)
4* FROM emp)
O exemplo no slide exibe o nome, o cargo e o salário de todos os funcionários cujo salário seja igual ao salário mínimo. A função de grupo MIN retorna um único valor para a consulta externa. 
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Usando a cláusula HAVING com Subconsultas
*
Marcelo Hely
*
 1 SELECT deptno, MIN(sal)
 2 FROM emp
 3 GROUP BY deptno
 4 HAVING MIN (sal) > ( SELECT MIN(sal)
 5 	 FROM emp
 6* 		 WHERE deptno = 20)
 A instrução SQL exibe todos os departamentos que tenham um salário mínimo maior que o departamento 20. O servidor Oracle executa primeiro as subconsultas e retorna os resultados para a cláusula HAVING.
Marcelo Hely
*
Marcelo Hely
*
Usando Funções de grupo em uma Subconsulta
Localize o cargo com o menor salário
1 SELECT job, AVG(sal)
2 FROM emp
3 GROUP BY job
4 HAVING AVG (sal) = ( SELECT 							 MIN(AVG(sal))
5 		 FROM emp
6* 			 GROUP BY job)
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Erros em Subconsultas
1 SELECT empno, ename
2 FROM emp
3 WHERE sal = (SELECT MIN(sal)
4 FROM emp
5* GROUP BY deptno)
Esta instrução gera um erro, pois, o operador de comparação de uma única linha espera somente um valor. A subconsulta contém a cláusula GROUP BY, o que indica que a subconsulta retornará várias linhas.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Erros em Subconsultas
Para corrigir esse erro, altere o operador = para IN.
1 SELECT empno, ename
2 FROM emp
3 WHERE sal IN (SELECT MIN(sal)
4 FROM emp
5* GROUP BY deptno)
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Problemas nas Subconsultas
*
Marcelo Hely
*
 1 SELECT ename, job
 2 FROM emp
 3 WHERE job = (SELECT job
 4 FROM emp
 5* WHERE ename = 'HELY')
 A subconsulta não retorna nenhum valor.
Marcelo Hely
*
Marcelo Hely
*
Problemas nas Subconsultas
*
Marcelo Hely
*
A intenção é localizar o funcionário cujo nome seja HELY. A instrução está correta, mas não seleciona linhas quando é executada. A consulta externa não localiza nenhum funcionário com um cargo igual a nulo e, assim não retorna linhas.
Marcelo Hely
*
Marcelo Hely
*
Subconsultas de várias linhas
Retorna mais de uma linha
Utiliza operadores de comparação de várias linhas
Operadores:
IN – Igual a qualquer membro da lista
ANY – Compare o valor a cada valor retornado pela subconsulta
ALL – Compare o valor a todo valor retornado pela subconsulta.
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Subconsultas de várias linhas
Localize os funcionários que recebam o mesmo salário que o salário mínimo de cada departamento.
1 SELECT ename, sal, deptno
2 FROM emp
3 WHERE sal IN (SELECT MIN(sal)
4 	 FROM emp
5* 	 GROUP BY deptno)
*
Marcelo Hely
*
Marcelo Hely
*
Marcelo Hely
*
Usando o Operador ANY em subconsultas de várias linhas
*
Marcelo Hely
*
1 SELECT empno,ename, job, sal
2 FROM emp
3 WHERE sal < ANY (SELECT sal
4 	 FROM emp
5 	 WHERE job = 'ANALYST')
6* AND job <> 'ANALYST'
Marcelo Hely
*
*
*
*
*
*
*
*