search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook

Difference between HAVING and WHERE in MySQL

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Statements and Clauses
chevron_right
Cookbooks
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more

It is recommended that you understand GROUP BY before trying to understand the difference between HAVING and WHERE.

Clause

Description

HAVING

specifies what condition should be applied after the aggregation takes place.

WHERE

specifies what records are eligible for grouping or aggregation in the first place. If a record does not meet the WHERE condition, it will not even be considered for GROUP BY and HAVING.

SELECT column_name(s)
FROM table_name
WHERE condition for records before grouping
GROUP BY column
HAVING condition for records after grouping;

Examples

Consider the following table about whether some students hold a drivers license:

student_id

full_name

age

has_license

1

Sky Towner

17

0

2

Ben Davis

19

1

3

Travis Apple

18

0

4

Arthur David

16

0

5

Benjamin Town

17

1

The above sample table can be created using the code here.

HAVING

To only return ages where there is only one student of that age:

SELECT age, COUNT(*)
FROM drivers_license
GROUP BY age
HAVING COUNT(*) = 1;
+------+----------+
| age | COUNT(*) |
+------+----------+
| 19 | 1 |
| 18 | 1 |
| 16 | 1 |
+------+----------+

Here we do not specify a WHERE clause hence all records are considered for grouping. We then use aggregate method COUNT to count the number of students of each age.

For ages where there is only one student of that age, we return the record. No record for age 17 is returned as there are two students (Sky Towner and Benjamin Town) who are 17.

Using HAVING with WHERE

To use a WHERE clause to filter out students with full_name='Sky Towner' before we perform grouping:

SELECT age, COUNT(*)
FROM drivers_license
WHERE full_name <> 'Sky Towner'
GROUP BY age
HAVING COUNT(*) = 1;
+------+----------+
| age | COUNT(*) |
+------+----------+
| 19 | 1 |
| 18 | 1 |
| 16 | 1 |
| 17 | 1 |
+------+----------+

This time we have 4 records returned as 'Sky Towner' is filtered out by the WHERE clause before we perform grouping. Therefore, of the records that were grouped, 'Benjamin Town' was the only 17 year old, hence the HAVING condition was fulfilled and age 17 was returned.

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!