WHERE Clause vs HAVING Clause
The WHERE Clause and HAVING clause filter the records in the Structured Query Language queries. The main difference between the WHERE clause and the HAVING clause is the WHERE clause is used to specify a condition for filtering records before any groupings are made. It cannot be used with an aggregate function, while the HAVING clause specifies conditions for filtering records from a group and its work with an aggregate function. Before comparing both clauses, we will understand each clause separately.
WHERE Clause
The WHERE clause in the Structured Query Language is used to specify the conditions for filtering the records. WHERE clause is used with the SELECT, INSERT, UPDATE and DELETE statement to filter records from the table.
The JOIN clause (WHERE and HAVING) mentions specific conditions to retrieve records from a single table or multiple tables. Not only to retrieve data but also to delete and update the records from the tables. It only retrieves the data, deletes, or updates the table if the mentioned condition is satisfied. The WHERE clause mention condition on the selected column or columns.
The Following Syntax of the WHERE Clause:
SELECT COLUMNS FROM TABLE_NAME WHERE CONDITIONS GROUP BY COLUMN;The WHERE Clause in the Structured Query Language can also use the Logical Operators, such as AND, OR, and NOT. The Logical operator expression uses the comparison operator as their operands. It also used the Comparison operator like <, <=, >, >= and <>. The Comparison operators are used to compare the values in the table, i.e., string values and arithmetic expressions.
Let's take an example of the WHERE clause. We have a table named Employees that contain the following records.
| EMPLOYEEID | FIRST_NAME | LAST_NAME | SALARY | CITY | DEPARTMENT | MANAGERID | WORKING_HOURS | GENDER |
| 1001 | VAIBHAVI | MISHRA | 65000 | PUNE | ORACLE | 1 | 12 | F |
| 1002 | VAIBHAV | SHARMA | 60000 | NOIDA | ORACLE | 1 | 9 | M |
| 1003 | NIKHIL | VANI | 50000 | JAIPUR | FMW | 2 | 10 | M |
| 2001 | PRACHI | SHARMA | 55500 | CHANDIGARH | ORACLE | 1 | 10 | F |
| 2002 | BHAVESH | JAIN | 65500 | PUNE | FMW | 2 | 12 | M |
| 2003 | RUCHIKA | JAIN | 50000 | MUMBAI | TESTING | 4 | 9 | F |
| 3001 | PRANOTI | SHENDE | 55500 | PUNE | JAVA | 3 | 9 | F |
| 3002 | ANUJA | WHERE | 50500 | JAIPUR | FMW | 2 | 9 | F |
| 3003 | DEEPAM | JAUHARI | 58500 | MUMBAI | JAVA | 3 | 12 | M |
| 4001 | RAJESH | GOUD | 60500 | MUMBAI | TESTING | 4 | 10 | M |
We will write the following query if we want to get the employee id, first name, last name, employee city, salary, and gender information whose salary is greater than 55000.
SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, CITY, SALARY, GENDER FROM EMPLOYEES WHERE SALARY >55000; The following output shows employee id, first name, last name, salary, city, and gender whose employee salary is greater than 55000.
The above query is just simply using the where clause. If we use the GROUP BY clause in the above query, the result will differ from the above result.
SELECT EMPLOYEE, FIRST_NAME, LAST_NAME, CITY, SALARY, GENDER FROM EMPLOYEES WHERE SALARY >55000 GROUP BY SALARY; It gives the following output:
We use the Salary field with GROUP BY Clause, which means first records get a filter specified in the WHERE Clause Condition and after the result is grouped using salary fields as we all can see the difference in both the output.
We will use AND logical operator with WHERE Clause.
In this example, we want to get employee id, first name, last name, city, salary whose salary is greater than 45000 and less than equal to 60000.
SELECT EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY FROM EMPLOYEES WHERE SALARY >= 45000 AND SALARY <= 60000.In the following output, we can see employee id, first name, last name, salary, and the city whose employee salary is greater than 45000 and less than equal to 60000.
HAVING Clause
HAVING Clause in Structured Query Language used with GROUP BY Clause specifies conditions that filter which group results appear in the output. It returns only those data from the group, which fulfills the conditions. With the HAVING clause, we can use the WHERE clause also in the query. If we use Both Clauses together, WHERE clause will get executed first where it will filter the individual rows, then rows are grouped and at the end, HAVING Clause filters the groups.
HAVING clause conditions are placed after the GROUP BY clause. HAVING clause behaved same as WHERE clause in the Structured Query Language does not use the GROUP BY clause. We can use aggregate functions such as MIN, MAX, SUM, AVG, and COUNT. This function is only used with the SELECT clause and the HAVING clause.
The Following Syntax of the WHERE Clause:
SELECT COLUMNS, AGGREGATE FUNCTION, FROM TABLENAME WHERE CONDITION GROUP BY COLUMN HAVING CONDITIONS; Let's take an example of the HAVING clause. We have a table named Employees that contain the following records.
| EMPLOYEEID | FIRST_NAME | LAST_NAME | SALARY | CITY | DEPARTMENT | MANAGERID | WORKING_HOURS | GENDER |
| 1001 | VAIBHAVI | MISHRA | 65000 | PUNE | ORACLE | 1 | 12 | F |
| 1002 | VAIBHAV | SHARMA | 60000 | NOIDA | ORACLE | 1 | 9 | M |
| 1003 | NIKHIL | VANI | 50000 | JAIPUR | FMW | 2 | 10 | M |
| 2001 | PRACHI | SHARMA | 55500 | CHANDIGARH | ORACLE | 1 | 10 | F |
| 2002 | BHAVESH | JAIN | 65500 | PUNE | FMW | 2 | 12 | M |
| 2003 | RUCHIKA | JAIN | 50000 | MUMBAI | TESTING | 4 | 9 | F |
| 3001 | PRANOTI | SHENDE | 55500 | PUNE | JAVA | 3 | 9 | F |
| 3002 | ANUJA | WHERE | 50500 | JAIPUR | FMW | 2 | 9 | F |
| 3003 | DEEPAM | JAUHARI | 58500 | MUMBAI | JAVA | 3 | 12 | M |
| 4001 | RAJESH | GOUD | 60500 | MUMBAI | TESTING | 4 | 10 | M |
Suppose we want to get the employee id, first name, last name, employee city, the average salary of employees, and gender information for employees whose employee average salary is greater than 50000 for this. In that case, we will write the following query.
SELECT EMPLOYEE, FIRST_NAME, LAST_NAME, CITY, AVG(SALARY) AS SALARY, GENDER FROM EMPLOYEES GROUP BY SALARY HAVING AVG(SALARY) >50000;It gives the following output:
Differences between Where Clause and HAVING Clause
Below are the following differences between the Where Clause and the HAVING clause, such as:
| WHERE Clause | HAVING Clause |
| WHERE clause is a pre-filter clause. | HAVING clause is a post-filter clause. |
| WHERE clause is used with SELECT statement, UPDATE statement, DELETE statement. | But without the SELECT statement, we cannot operate using the HAVING clause. |
| The WHERE clause fetches the records from the table on the given specified conditions. | Whereas the HAVING clause retrieves entire records from the table, grouping is done based on the condition. |
| WHERE clause is used in the query to filter the individual rows. | On the other hand, the HAVING clause filters the records based on the groups, not individual rows. |
| We use the WHERE clause to filter individual rows, so we cannot use aggregate functions with the WHERE clause because the aggregate function is used for the entire columns. | But we can use aggregate functions with the HAVING clause because the HAVING clause is used to filter the entire column (groups). |
| We can say the WHERE clause is faster than HAVING because the WHERE clause is placed before the GROUP BY clause, which means rows get filtered first, then the operation is formed on the aggregate operation or function. | HAVING clause is placed after the GROUP BY clause, which means aggregate function calculation is performed, then the HAVING clause filters the data. Apart from this situation, we can say the WHERE clause is faster than the HAVING clause, so it's better to avoid the HAVING clause wherever possible. |