SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDWORKING_HOURSGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE112F
1002VAIBHAVSHARMA60000NOIDAORACLE19M
1003NIKHILVANI50000JAIPURFMW210M
2001PRACHISHARMA55500CHANDIGARHORACLE110F
2002BHAVESHJAIN65500PUNEFMW212M
2003RUCHIKAJAIN50000MUMBAITESTING49F
3001PRANOTISHENDE55500PUNEJAVA39F
3002ANUJAWHERE50500JAIPURFMW29F
3003DEEPAMJAUHARI58500MUMBAIJAVA312M
4001RAJESHGOUD60500MUMBAITESTING410M

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.

WHERE Clause vs HAVING Clause

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:

WHERE Clause vs HAVING Clause

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.

WHERE Clause vs HAVING Clause

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.

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDWORKING_HOURSGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE112F
1002VAIBHAVSHARMA60000NOIDAORACLE19M
1003NIKHILVANI50000JAIPURFMW210M
2001PRACHISHARMA55500CHANDIGARHORACLE110F
2002BHAVESHJAIN65500PUNEFMW212M
2003RUCHIKAJAIN50000MUMBAITESTING49F
3001PRANOTISHENDE55500PUNEJAVA39F
3002ANUJAWHERE50500JAIPURFMW29F
3003DEEPAMJAUHARI58500MUMBAIJAVA312M
4001RAJESHGOUD60500MUMBAITESTING410M

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:

WHERE Clause vs HAVING Clause

Differences between Where Clause and HAVING Clause

Below are the following differences between the Where Clause and the HAVING clause, such as:

WHERE ClauseHAVING 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.



ADVERTISEMENT
ADVERTISEMENT