Saturday, January 24, 2015

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:

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.

      SELECT *
          FROM emp
          WHERE deptno = 10;

------  -----     ------    ------  ----------   -----    -----   ------
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

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.

      SELECT deptno, SUM(sal)
           FROM emp
           GROUP BY deptno;

---------- ----------
        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.

      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

    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.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect