Difference Between WHERE Clause AND HAVING Clause in Oracle

‘Where’ and ‘Having’ clause both are used to filter the rows fetched from select statement however there is a difference:

WHERE CLAUSE

Using where clause we can restrict or filter the records that we get from select statement.

Let say from the select statement we are fetching 10 records but we wanted to display 3 records.

In the below example we are trying to fetch only the employee records for deptno = 10.

Ex:

      SELECT *

          FROM emp

          WHERE deptno = 10;

EMPNO   ENAME      JOB       MGR    HIREDATE      SAL     COMM    DEPTNO

------  -----     ------    ------  ----------   -----    -----   ------

7782    CLARK      MANAGER   7839   6/9/1981      2450            10

7839    KING       PRESIDENT        11/17/1981    5000            10

7934    MILLER     CLERK     7782   1/23/1982     1300            10

HAVING

Before we discuss ‘Having’ clause lets understand ‘Group by’ clause first.

Using group by, we can create groups of related information.

Columns used in ‘select’ must be used with ‘group by’, otherwise system does not recognize it as a group by expression.

In the below example we are trying to display sum of salaries at each dept level.

When SQL statement is run, data fetched from the database is grouped first based on deptno and then sum() function is applied to get the desired result.

Ex:

      SELECT deptno, SUM(sal)

           FROM emp

           GROUP BY deptno;

   DEPTNO   SUM(SAL)

---------- ----------

        10       8750

        20      10875

        30       9400

In this example we are trying to group the records using two columns deptno and job

SELECT deptno,job,SUM(sal)

            FROM emp

            GROUP BY deptno,job;

    DEPTNO    JOB         SUM(SAL)

   -------- ---------   ----------

        10   CLERK         1300

        10   MANAGER       2450

        10   PRESIDENT     5000

        20   ANALYST       6000

        20   CLERK         1900

        20   MANAGER       2975

        30   CLERK         950

        30   MANAGER       2850

        30   SALESMAN      5600

Having Clause:

Having clause is used to restrict the records that are grouped using group by clause.

Having clause is just like where clause but it can be used only with group by as we cannot use where clause in group by.

Having clause works or can be used, only in the combination with group by clause.

In the below example we are trying to fetch all deptno and job records whose aggregated salary of its employees is greater than 3000 ( Sum(sal) > 3000 ).

Having clause gets executed after the records are fetched and grouped.

Ex:

      SELECT deptno,job,SUM(sal) tsal

          FROM emp

          GROUP BY deptno,job

          HAVING SUM(sal) > 3000;

       DEPTNO      JOB           TSAL

      ----------  ---------    --------

        10       PRESIDENT       5000

        20       ANALYST         6000

        30       SALESMAN        5600

In this example, after applying the having clause we are trying to order the records by JOB.

SELECT deptno,job,SUM(sal) tsal

        FROM emp

        GROUP BY deptno,job

        HAVING SUM(sal) > 3000

        ORDER BY job;

    DEPTNO      JOB         TSAL

 ----------    ---------   -------

      20        ANALYST     6000

      10        PRESIDENT   5000

      30        SALESMAN    5600

ORDER OF EXECUTION

    Group the rows together based on group by clause.

    Calculate the group functions for each group.

    Choose and eliminate the groups based on the having clause.

    Order the groups based on the specified column.