Oracle SQL Where and Having clauses

Difference between WHERE and HAVING clauses

difference_between_where_and_having_clauses

Though it appears that both clauses do the same thing, they do it in different ways. In fact, their functions complement each other.

  • A WHERE clause is used is filter records from a result. The filter occurs before any groupings are made.
  • A HAVING clause is used to filter values from a group.

The format of an SQL Statement :

SELECT
FROM
WHERE
GROUP BY
HAVING

To help keep things straight think like the order of execution of SQL statements as from top to bottom. That means the WHERE clause is first applied to the result and then, the remaining rows summarized according to the GROUP BY.

WHERE clause

The WHERE clause is used to filer rows from a results. For instance

SELECT COUNT(*) FROM emp;

returns 14 as the count, whereas, the query

SELECT COUNT(*) FROM emp WHERE sal > 1000;

Returns 12 as the count. This is because the WHERE clause filters out the 2 employees whose salary is less than or equal to 1000 from the results.

HAVING Clause

The HAVING clause is used to filter values in a GROUP BY. You can use them to filter out groups such as

SELECT empno,SUM(comm) commisssion FROM emp GROUP BY empno HAVING empno > 7500;

But their true power lies in their ability to compare and filter based on aggregate function results. For instance, you can select all employees whose total commission is more than 0.

SELECT empno,SUM(comm) commisssion FROM emp GROUP BY empno HAVING SUM(comm) > 0;

Since the WHERE clause's visibility is one row at a time, there isn't a way for it to evaluate the SUM across all Employees. The HAVING clause is evaluated after the grouping is created.

Combining the two: WHERE and HAVING

When SQL statements have both a WHERE clause and HAVING clause, keep in mind the WHERE clause is applied first, then the results grouped, and finally the groups filtered according to the HAVING clause.

  1. Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work :
           UPDATE dept SET dname = 'Accounting' WHERE deptno = 10; --works fine
        
           UPDATE dept SET dname = 'Accounting' Having deptno = 10; --error
    
  2. WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
  3. One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
  4. When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.