| Subconsulta que devolve várias linhas - operadores do SQL | |
Os exemplos de subconsulta que vimos até agora devolvem apenas uma linha. Mas uma subconsulta pode devolver várias linhas, o que obriga a consulta externa a ter cuidados especiais e a recorrer aos operadores do SQL:
Quando a subconsulta devolve várias linhas o operador IN pode ser usado para validar se uma linha da consulta externa está presente no conjunto criado pela subconsulta. Devolve TRUE se o(s) valor(es) usado(s) na consulta externa está(ão) incluído(s) no conjunto devolvido pela consulta interna. Este operador pode ser negado com NOT.
Suponha que pretende determinar os empregados que ganham o salário mais baixo em cada função, usando a tabela EMPLOYEES. A consulta abaixo tenta responder a esta pergunta, mas sem sucesso.
select first_name,salary,job_id
from employees
where salary in (select min(salary)
from employees
group by job_id)
order by job_id, salary;
|
FIRST_NAME SALARY JOB_ID -------------------- ---------- ---------- Randall 2500 SH_CLERK Steven 24000 AD_PRES Neena 17000 AD_VP Lex 17000 AD_VP Bruce 6000 IT_PROG Diana 4200 IT_PROG Nancy 12008 FI_MGR Luis 6900 FI_ACCOUNT Den 11000 PU_MAN Karen 2500 PU_CLERK Shanta 6500 ST_MAN Kevin 5800 ST_MAN James 2500 ST_CLERK TJ 2100 ST_CLERK Joshua 2500 ST_CLERK Peter 2500 ST_CLERK Gerald 11000 SA_MAN Eleni 10500 SA_MAN Peter 10000 SA_REP Janette 10000 SA_REP Clara 10500 SA_REP Harrison 10000 SA_REP Sundita 6100 SA_REP Ellen 11000 SA_REP Martha 2500 SH_CLERK Nandita 4200 SH_CLERK Jennifer 4400 AD_ASST Michael 13000 MK_MAN Pat 6000 MK_REP Susan 6500 HR_REP Hermann 10000 PR_REP Shelley 12008 AC_MGR William 8300 AC_ACCOUNT 33 rows selected |
A forma de associar a função ao salário mínimo é devolver e comparar o par de valores, como no exemplo abaixo:
select first_name,salary,job_id
from employees
where (job_id,salary) in (select job_id,min(salary)
from employees
group by job_id)
order by job_id, salary;
|
FIRST_NAME SALARY JOB_ID -------------------- ---------- ---------- William 8300 AC_ACCOUNT Shelley 12008 AC_MGR Jennifer 4400 AD_ASST Steven 24000 AD_PRES Lex 17000 AD_VP Neena 17000 AD_VP Luis 6900 FI_ACCOUNT Nancy 12008 FI_MGR Susan 6500 HR_REP Diana 4200 IT_PROG Michael 13000 MK_MAN Pat 6000 MK_REP Hermann 10000 PR_REP Karen 2500 PU_CLERK Den 11000 PU_MAN Eleni 10500 SA_MAN Sundita 6100 SA_REP Randall 2500 SH_CLERK Martha 2500 SH_CLERK TJ 2100 ST_CLERK Kevin 5800 ST_MAN 21 rows selected |
O operador ANY (e o seu sinónimo SOME) permite a uma consulta externa fazer comparações usando < ou > com os elementos de um conjunto devolvido pela subconsulta. Este operador devolve TRUE se uma das linhas do conjunto satisfaz a condição, ou seja, devolve FALSE se nenhuma satisfaz a condição. Este operador pode ser negado com NOT.
A consulta abaixo devolve os empregados que ganham mais que algum empregado do departamento 30. Isto é o mesmo que afirmar que procuramos os que ganham mais que o salário mínimo do departamento 30.
select ename, sal, job, deptno
from emp
where sal > ANY (select distinct sal
from emp
where deptno=30);
|
ENAME SAL JOB DEPTNO ---------- ---------------------- --------- ---------------------- KING 5000 PRESIDENT 10 FORD 3000 ANALYST 20 SCOTT 3000 ANALYST 20 JONES 2975 MANAGER 20 BLAKE 2850 MANAGER 30 CLARK 2450 MANAGER 10 ALLEN 1600 SALESMAN 30 TURNER 1500 SALESMAN 30 MILLER 1300 CLERK 10 WARD 1250 SALESMAN 30 MARTIN 1250 SALESMAN 30 ADAMS 1100 CLERK 20 12 rows selected |
O operador ALL permite a uma consulta externa fazer comparações usando < ou > com os elementos de um conjunto devolvido pela subconsulta. Este operador devolve TRUE se todas as linhas do conjunto satisfazem a condição, ou seja, devolve FALSE se alguma linha não a satisfaz. Este operador pode ser negado com NOT.
A consulta abaixo devolve os empregados que ganham mais que todos os empregados do departamento 30. Isto é o mesmo que afirmar que procuramos os que ganham mais que o salário máximo do departamento 30.
select ename, sal, job, deptno
from emp
where sal > ALL (select distinct sal
from emp
where deptno=30);
|
ENAME SAL JOB DEPTNO ---------- ---------------------- --------- ---------------------- JONES 2975 MANAGER 20 SCOTT 3000 ANALYST 20 KING 5000 PRESIDENT 10 FORD 3000 ANALYST 20 4 rows selected |
O operador EXISTS permite à consulta externa verificar se a consulta interna devolveu alguma linha. Não se preocupa com o valor das linhas, mas sim com a cardinalidade do conjunto. Devolve TRUE se a cardinalidade for superior a 0 (zero) e FALSE caso seja igual a 0 (zero). Este operador pode ser negado com NOT.
O exemplo abaixo procura os empregados que tenham pelo menos um subordinado:
select m.empno,m.ename,m.job,m.deptno
from emp m
where exists (select e.empno
from emp e
where e.mgr=m.empno)
|
EMPNO ENAME JOB DEPTNO ---------------------- ---------- --------- ---------------------- 7902 FORD ANALYST 20 7698 BLAKE MANAGER 30 7839 KING PRESIDENT 10 7566 JONES MANAGER 20 7788 SCOTT ANALYST 20 7782 CLARK MANAGER 10 6 rows selected |
O exemplo abaixo procura todos os departamentos que não possuem empregados:
select d.deptno, d.dname
from dept d
where not exists (select deptno
from emp e
where e.deptno=d.deptno);
|
DEPTNO DNAME ---------------------- -------------- 40 OPERATIONS 1 rows selected |
O query interno necessita devolver um valor que não tem que ser a coluna que está em causa. O exemplo anterior pode ser substituído por este:
select d.deptno, d.dname
from dept d
where not exists (select 1
from emp e
where e.deptno=d.deptno);
|
DEPTNO DNAME ---------------------- -------------- 40 OPERATIONS 1 rows selected |
Uma consulta ao dicionário de dados mostra que o relacionamento entre as tabelas EMP e DEPT não está assegurado por uma restrição definida no motor da base de dados. Isto significa que é possível que exista um empregado que trabalhe num departamento que não esteja definido na tabela DEPT. A consulta abaixo pesquisa isso:
select e.empno, e.ename, e.deptno
from emp e
where not exists (select deptno
from dept
where dept.deptno=e.deptno);
|
EMPNO ENAME DEPTNO ---------------------- ---------- ---------------------- 0 rows selected |
Realizado por Turtle Learning ®. Última alteração em 2011-08-14
Topo