What is the difference between “HAVING” and “WHERE” clause

by

“HAVING” clause is used to specify filtering criteria for “GROUP BY”, while “WHERE” clause applies on normal SQL.

The “WHERE” and “HAVING” clauses in a database management system (DBMS) both filter data, but they operate at different stages of the query process and are used for different purposes.

  1. WHERE Clause:
    • The “WHERE” clause is used to filter rows before any groupings are made.
    • It is used with the “SELECT”, “UPDATE”, and “DELETE” statements to specify the conditions that must be met for a row to be included in the result set.
    • It operates on individual rows.
    • It is typically used with columns that are not part of aggregate functions like SUM, AVG, etc.

Example:

sql
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';
  1. HAVING Clause:
    • The “HAVING” clause is used to filter rows after the grouping has been done.
    • It is used with the “SELECT” statement in conjunction with the “GROUP BY” clause to specify the conditions that must be met by groups returned by the GROUP BY clause.
    • It operates on aggregated data.
    • It is typically used with aggregate functions like SUM, AVG, etc.

Example:

sql
SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > 1000;

In summary, while both clauses are used for filtering data, the “WHERE” clause operates on individual rows before any grouping, and the “HAVING” clause operates on grouped rows after grouping has been performed.