Difference Between WHERE Clause and HAVING Clause

Learn about the Difference Between WHERE Clause and HAVING Clause in MySQL.
Submitted by Apurva Mathur, on November 08, 2022

In MySQL we have some clauses; these clauses help us to filter the data as per our requirements. The clauses play an important part when you write queries. We have many clauses in MySQL.

WHERE Clause

The WHERE Clause is used in the situation when you want some specific record. We can use this clause with INSERT, UPDATE, SELECT, or DELETE any of these statements. We just have to write a few conditions and we'll get the result according to our applied condition.

Syntax:

WHERE (conditions);

Example:

Suppose I have a table named "students" and inside this table, I have the following columns:

MySQL | WHERE Clause and HAVING Clause (1)

Now if I want to know the details of the "students" who have "pcmb" as a subject then in such I can use the WHERE clause over here in the respective way:

SELECT * FROM students WHERE Subjects='PCMB';
MySQL | WHERE Clause and HAVING Clause (2)

As you can see, the result only contains the "students" who have "PCMB" subject.

HAVING Clause

When we use an aggregate function in our queries there we cannot use WHERE conditions, in such case MySQL provides us HAVING clause, this clause is used wherever we are not able to use the WHERE clause. This clause is used with the GROUP BY clause and returns the rows where the condition is TRUE.

Syntax:

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition;

Examples:

Suppose I have a table named as "Marks" and inside this table we have following columns:

MySQL | WHERE Clause and HAVING Clause (3)

Now if we need to find out the minimum marks among all the students, then in such case we will make our query in such a way that they are grouped by status.

SELECT status, sname,min(total_marks) AS "Minimum marks" 
FROM marks
GROUP BY status
HAVING min(total_marks)>10;

The above query will give the following result,

MySQL | WHERE Clause and HAVING Clause (4)

Overview Differences

WHERE Clause HAVING Clause
This type of clause focuses on filtering the rows individually. This type of clause focuses on filtering the rows based on GROUP.
We can use WHERE CLAUSE with other DML language statements like SELECT, INSERT, UPDATE, DELETE. HAVING CLAUSE can only be used with SELECT statement.
It is not possible to use aggregate functions with WHERE CLAUSE. We can easily use aggregate function with HAVING CLAUSE.
In row operations, the WHERE Clause is implemented. In column operations, the HAVING Clause is implemented.
This clause is independent of the GROUP BY clause. This clause is dependent on GROUP BY clause.

ADVERTISEMENT
ADVERTISEMENT




Comments and Discussions!




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.