WHERE clause instead of HAVING clause

In general we should avoid using HAVING clause in SELECT statements because it filters selected rows only after all the records are been fetched where it might include sorting, summing etc… It is only used to filter when we have a SELECT statement which contains group functions on it.

For example let us take EMP table from SCOTT database and check for all the employee name and it’s salary having salary grater then 2000.

Using HAVING clause:  

SQL> EXPLAIN PLAN FOR
SELECT ename ,sal
FROM emp
GROUP BY ename, sal
HAVING sal >2000;

PLAN_TABLE_OUTPUT

Plan hash value: 2138686577

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 130 | 4 (25)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 13 | 130 | 4 (25)| 00:00:05 |
| 3 | TABLE ACCESS FULL| EMP | 17 | 170 | 3 (0)| 00:00:04 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("SAL">2000)

Using WHERE clause:

SQL> EXPLAIN PLAN FOR
SELECT ename ,sal
 FROM emp
 WHERE sal >2000
GROUP BY ename, sal;

PLAN_TABLE_OUTPUT

Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 120 | 4 (25)| 00:00:05 |
| 1 | HASH GROUP BY | | 12 | 120 | 4 (25)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| EMP | 12 | 120 | 3 (0)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("SAL">2000)

From the above two SQL statements we can see the the best plan that we got by using WHERE clause in place of HAVING clause.

Advertisements

One thought on “WHERE clause instead of HAVING clause

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s