Database Related

SQL – Difference Between The HAVING Clause And The WHERE Clause

To begin with, in SQL, a HAVING clause indicates that only rows should be returned in the particular SQL SELECT statement in conditions where the aggregate values meet up with the agreed conditions. Since the WHERE keyword cannot be used together with aggregate functions, the HAVING clause was implemented to serve that purpose.

Subsequently, the WHERE clause in SQL is primarily used when data is retrieved from a table or when multiple tables are joined. It serves as a condition specifier to state what exactly needs to be added, removed or the particular fields that need to be joined. It returns specific values from the table when the stated condition is satisfied or met. This clause is used to filter the specific field that needs to be used at that particular moment in time. One other thing to note is that, it must not necessarily be used with the SELECT statement, but can also be used in conjunction with the DELETE, UPDATE statement etc.

Differences between the HAVING clause and the WHERE clause

WHERE clause and HAVING clause in SQL server are utilized to channel the qualities that we get from the tables in SQL server. There are couple of contrasts between WHERE clause and HAVING clause in SQL server which are given underneath:

  1. The WHERE clause is used to stipulate the criteria which individual data or records must meet to be chosen by a query. While the HAVING clause can’t be utilized without the GROUP BY clause.
  2. The WHERE clause is used to select rows in a table right before grouping the items. The HAVING clause however, is used to select rows within the table after grouping the items.
  3. The WHERE clause won’t be able to hold aggregate functions. While the HAVING clause can hold and maintain aggregate functions.

Examples of Where Clause

Select EmployeeID, Name, Salary from Employee where Salary > 10000

Example of the Having Clause

Select EmployeeID, Name, MAX(Salary) from Employee group by EmployeeID,Name having Salary > 10000

There are some performance issues with the HAVING clause as the result set is generated after the group by clause is used whereas in the WHERE clause you do not need to use the group by clause before filtering the result set.

Leave a Reply