Oracle SQL - HAVING Clauses Without Group Functions

  1. HOME
  2. Oracle SQL
  3. Select
  4. HAVING Clause

Introduction

SQL language allows you to write queries with a HAVING clause without a preceding GROUP BY clause.

In that case, Oracle assumes an implicit GROUP BY on a constant expression, the full table is treated as a single group.

In the following code, the second query is much more efficient than the first one, because it filters out rows using the WHERE clause before aggregation rather than afterward using the HAVING clause.

You should always filter as early as possible in a multi-step operation. 

select deptno, count(*)
from   emp
group  by deptno
having deptno <= 20;

select deptno, count(*)
from   emp
where  deptno <= 20
group  by deptno;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   ww  w .  j a va 2 s  .c  o  m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL> select deptno, count(*)
  2  from   emp
  3  group  by deptno
  4  having deptno <= 20;

   DEPTNO |  COUNT(*)
--------- | ---------
    2     |  00005.00
    1     |  00005.00

SQL>
SQL> select deptno, count(*)
  2  from   emp
  3  where  deptno <= 20
  4  group  by deptno;

   DEPTNO |  COUNT(*)
--------- | ---------
    2     |  00005.00
    1     |  00005.00

SQL>

Related Topic