A cláusula HAVING

A cláusula WHERE é utilizada no "modo de linha" para filtrar linhas (restrição horizontal). A cláusula HAVING tem funções semelhantes no "modo de grupo": serve para filtrar grupos quando o query possui um GROUP BY.

Neste ponto veremos:

• Como utilizar a cláusula HAVING?
• Considerações sobre desempenho entre WHERE e HAVING;

Como utilizar a cláusula HAVING?

O exemplo abaixo determina o salário médio de todos os departamentos que possuem mais de três pessoas:

select deptno, avg(sal), count(*)
from emp
group by deptno
having count(*) > 3;

DEPTNO                 AVG(SAL)                                  COUNT(*)               
---------------------- ----------------------------------------- ---------------------- 
30                     1566,666666666666666666666666666666666667 6                      
20                     2175                                      5                      

2 rows selected

O exemplo abaixo determina as funções cujo salário mais alto é igual ou superior a 3000:

select job,max(sal)
from emp
group by job
having max(sal) >= 3000;

JOB       MAX(SAL)               
--------- ---------------------- 
PRESIDENT 5000                   
ANALYST   3000                   

2 rows selected 

A execução de uma consulta com WHERE, GROUP BY e HAVING segue os passos abaixo:

1. WHERE - para filtrar as linhas individuais (não pode conter funções de grupo);
2. GROUP BY - para agrupar as linhas seleccionadas no passo anterior;
3. HAVING - para excluir os grupos não requeridos;

A cláusula WHERE não pode incluir funções de grupo, pois a sua missão é filtrar linhas e não grupos. Durante a sua execução os grupos ainda não estão formados, pelo que as funções de grupo não podem ser calculadas.

O exemplo abaixo pretende determinar os departamentos cujo salário médio é superior a 2000:

select deptno,avg(sal)
from emp
where avg(sal) > 2000
group by deptno;

where avg(sal) > 2000
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

A mensagem de erro mostra o principio referido anteriormente: a cláusula WHERE não pode receber funções de grupo. A forma de resolver este problema é usando a cláusula HAVING:

select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;

DEPTNO                 AVG(SAL)               
---------------------- ----------------------------------------- 
20                     2175                   
10                     2916,666666666666666666666666666666666667 

2 rows selected

Topo

Considerações sobre desempenho entre WHERE e HAVING

Algumas consultas com grupos tanto podem ser resolvidas recorrendo a uma cláusula WHERE como a uma cláusula HAVING. Considere que pretende determinar o salário médio de cada função (JOB) excluíndo os 'MANAGER'. A solução abaixo filtra as funções usando uma cláusula WHERE:

select job,avg(sal)
from emp
where job !='MANAGER'
group by job;

JOB       AVG(SAL)               
--------- ---------------------- 
CLERK     1037,5                 
SALESMAN  1400                   
PRESIDENT 5000                   
ANALYST   3000                   

4 rows selected

O mesmo resultado pode ser obtido recorrendo a um filtro de grupos, usando HAVING:

select job,avg(sal)
from emp
group by job
having job != 'MANAGER';

JOB       AVG(SAL)               
--------- ---------------------- 
CLERK     1037,5                 
SALESMAN  1400                   
PRESIDENT 5000                   
ANALYST   3000                   

4 rows selected

A cláusula WHERE eliminou todos os elementos que iriam formar o conjunto MANAGER, e por isso este não foi formado. Embora ambos queries tenham resolvido o problema (foram eficazes), as duas consultas não são igualmente eficientes.

A primeira consulta segue o seguinte plano de execução:

1. Identificar todas as linhas da tabela excluíndo as que têm função MANAGER;
2. Ordenar as linhas por JOB;
3. Criar os conjuntos por JOB (agrupar);
4. Determinar a média de cada conjunto;

A segunda consulta segue este plano de execução:

1. Identificar todas as linhas da tabela;
2. Ordenar as linhas por JOB;
3. Criar os conjuntos por JOB (agrupar);
4. Determinar a média de cada conjunto;
5. Eliminar o conjunto 'MANAGER';

Comparando os dois planos de execução verificamos que no primeiro as linhas são filtradas no inicio, o que significa que as operações de ordenação e agrupamento são feitas sobre menos linhas. No segundo plano os grupos são formados tendo em conta todas as linhas, sendo depois eliminado um dos grupos. Quando as tabelas têem grande dimensão esta diferença é significativa, tornando a primeira consulta mais eficiente pelos seguintes motivos:

1. Tem menos linhas para ordenar;
2. Tem menos contas para fazer, já que há menos linhas e menos grupos;
3. Não deita fora trabalho já feito;

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-02-26