| Subconsulta e cláusulas da instrução SELECT | |
Uma subconsulta pode ser colocada em vários pontos da instrução SELECT:
Nos exemplos apresentados até aqui a subconsulta aparece na cláusula WHERE.
O exemplo abaixo procura todos os empregados que executam a mesma função que BLAKE (subconsulta simples):
select ename,job
from emp
where job = (select job
from emp
where ename = 'BLAKE');
|
EENAME JOB ---------- --------- JONES MANAGER BLAKE MANAGER CLARK MANAGER 3 rows selected |
A consulta abaixo procura os empregados que ganham um salário superior ao salário médio do respectivo departamento (subconsulta correlacionada):
select e1.empno, e1.ename, e1.sal, e1.deptno
from emp e1
where e1.sal > (select avg(e2.sal)
from emp e2
where e2.deptno = e1.deptno);
|
EMPNO ENAME SAL DEPTNO ---------------------- ---------- ---------------------- ---------------------- 7499 ALLEN 1600 30 7566 JONES 2975 20 7698 BLAKE 2850 30 7788 SCOTT 3000 20 7839 KING 5000 10 7902 FORD 3000 20 6 rows selected |
Subconsulta na cláusula HAVING
Uma subconsulta pode ser colocada na cláusula HAVING.
O exemplo abaixo determina os departamentos cujo salário médio é superior ao do departamento 30:
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > (select avg(sal)
from emp
where deptno=30);
|
DEPTNO AVG(SAL) ---------------------- ---------------------- 20 2175 10 2916,666666666666666666666666666666666667 2 rows selected |
O exemplo abaixo determina a função com o salário médio mais elevado:
select job, avg(sal)
from emp
group by job
having avg(sal) = (select max(avg(sal))
from emp
group by job);
|
JOB AVG(SAL) --------- ---------------------- PRESIDENT 5000 1 rows selected |
Uma subconsulta pode ser colocada na cláusula FROM, o que é o mesmo que referir uma view (vista) ou uma tabela virtual.
O exemplo abaixo determina os 5 empregados que têm o salário mais elevado:
select *
from (select ename, sal
from emp
order by sal desc)
where rownum <=5;
|
ENAME SAL ---------- ---------------------- KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850 5 rows selected |
A consulta abaixo lista os salários máximos da tabela EMP e EMPLOYEES usando apenas um query e mostrando os resultados numa linha única:
select e1.c1, e2.c1 from (select max(sal) c1 from emp) e1 , (select max(salary) c1 from employees) e2; |
C1 C1 ---------------------- ---------------------- 5000 24000 1 rows selected |
Subconsulta na cláusula SELECT
As subconsultas podem ser colocadas na cláusula SELECT.
O exemplo abaixo pretende listar os salários máximos da tabela EMP e EMPLOYEES usando apenas um query e mostrando os resultados numa linha única:
select (select max(sal) from emp), (select max(salary) from employees) from dual; |
(SELECTMAX(SAL)FROMEMP) (SELECTMAX(SALARY)FROMEMPLOYEES) ----------------------- -------------------------------- 5000 24000 1 rows selected |
O exemplo abaixo considera apenas os empregados do departamento 20 que executam a função 'CLERK'. Pretende mostrar o salário de cada um e compará-lo com a média da função e a média do departamento.
select ename, sal, job, (select round(avg(sal)) from emp where job='CLERK') "Avg_Sal_Job", deptno, (select round(avg(sal)) from emp where deptno=20) "Avg_Sal_Deptno" from emp where job='CLERK' and deptno=20; |
ENAME SAL JOB Avg_Sal_Job DEPTNO Avg_Sal_Deptno ---------- ---------------------- --------- ---------------------- ---------------------- ---------------------- SMITH 800 CLERK 1038 20 2175 ADAMS 1100 CLERK 1038 20 2175 2 rows selected |
Vamos generalizar o exemplo anterior para todos os empregados da empresa, o que nos obriga a considerar todas as funções e todos os departamentos. Vamos recorrer a subconsultas correlacionadas.
select e1.ename, e1.sal, e1.job, (select round(avg(e2.sal)) from emp e2 where e2.job=e1.job) "Avg_Sal_Job", e1.deptno, (select round(avg(e3.sal)) from emp e3 where e3.deptno=e1.deptno) "Avg_Sal_Deptno" from emp e1; |
ENAME SAL JOB Avg_Sal_Job DEPTNO Avg_Sal_Deptno ---------- ---------------------- --------- ---------------------- ---------------------- ---------------------- SMITH 800 CLERK 1038 20 2175 ALLEN 1600 SALESMAN 1400 30 1567 WARD 1250 SALESMAN 1400 30 1567 JONES 2975 MANAGER 2758 20 2175 MARTIN 1250 SALESMAN 1400 30 1567 BLAKE 2850 MANAGER 2758 30 1567 CLARK 2450 MANAGER 2758 10 2917 SCOTT 3000 ANALYST 3000 20 2175 KING 5000 PRESIDENT 5000 10 2917 TURNER 1500 SALESMAN 1400 30 1567 ADAMS 1100 CLERK 1038 20 2175 JAMES 950 CLERK 1038 30 1567 FORD 3000 ANALYST 3000 20 2175 MILLER 1300 CLERK 1038 10 2917 14 rows selected |
Realizado por Turtle Learning ®. Última alteração em 2011-08-14
Topo