Difference between Where and Having Clause in SQL | SQL Fundamentals

Fundamentally there are 2 ways to filter data in SQL : Where and Having clause. But when to use which one is sometimes confusing. In this video we will understand the difference between the 2 and have clarity on when to use which one.

[shikhasharma]

Order of commands also matters we can never use having before where.

Because in the backend order of execution goes as below:

Query Process Steps
1. Getting Data (From, Join)
2. Row Filter (Where)
3. Grouping (Group by)
4. Group Filter (Having)
5. Return Expressions (Select)
6. Order & Paging (Order by & Limit / Offset)

[rakeshgopidi]

## Difference between WHERE and Having - interview point of view notes
==> WHERE clause cannot be used with aggregates where as HAVING can. This means WHERE clause is used for filtering individual rows where as HAVING clause is used to filter groups.
==> WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
==> WHERE and HAVING can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.
==> So from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
==> Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY.

[sannigupta]

create table employees
(emp_id int,
emp_name varchar(100),
department_id int,
salary int,
manager_id int);

insert into employees
values
(1, 'Ankit', 100, 10000, 4),
(2, 'Mohit', 100, 15000, 5),
(3, 'Vikas', 100, 10000, 4),
(4, 'Rohit', 100, 5000, 2),
(5, 'Mudit', 200, 12000, 6),
(6, 'Agam', 200, 12000, 2),
(7, 'Sanjay', 200, 9000, 2),
(8, 'Ashish', 200, 5000, 2)

Please find the DDL for this tutorial. Thanks.

[muhammedhashir]

Thanks Ankit!
Key Takeaways
Where - to filter records - checks record/rowwise
Having - to filter on top of aggregation
If both are used where will get executed before having.

[abhisheksinghchauhan]

Both are used to filter the records.
This is the similarity in these.
WHERE is used before aggregation and having is used after aggregation.

HAVING CLAUSE is used only when there is an aggregation and group by.

Where clause can be used without aggregation.

[yashdhas]

Thank you.
You are clearing concepts in most easiest way.

[equiwave]

Hi Ankit,

This playlist is useful to level up on SQL skills. I have started with this "SQL Tips and Tricks" playlist and will move on to the other useful playlists.
A very clear and concise manner of explaining the important difference between the WHERE and the HAVING clause.
Thanks for all your efforts!!! 👍

Regards,
Deepak.

[datascienceenthusiast]

No doubt its adding value to all SQL beginners .

[AlbouryNdiaye-roqz]

Hi Ankit - Clear and to the point as usual. Thank you so much for sharing your knowledge!

[ManikandanRaju]

Clear and concise. Thank you for the video

[modimansii]

Basic but important concept nicely explained.

[riyatiwari]

Thank you Ankit for breaking down the difference very clearly and easily. Looking forward to learn more. :)

[amritasharma]

Sir date-time function related concepts please also Paytm interview questions related to sql. Cross join and especially subquries. I'm getting stuck in subqueries.

[florincopaci]

All your videous are very useful! Keep going! Thank you!

[yashsoni]

Ankit your videos are very helpful and very easy to understand.
but please also provide the table data so that we can practice and can also save the query for revision in future.
please

[asif_]

can you plz make intermediate or advance video course plz
your explanition is great

[reddeppareddy]

The Having Clause will be used with the filtered value, But the question was, find the average salaries of departments > 10000, Then for AVG we have to consider all the values in each department, Am i right?

[kumarr]

Thanks for the video. very informative. Could you please provide datasets used in the videos.

[Shri_Xin_Ping]

Last query is not clear..where should not be used with aggregated function i.e (avg) but u used it sir ?why

[shaktii]

kya aap sql query pe ek series bna sakte ho...mujhe sql ki query likhna sikhna h...basic, complex and joins etc but hindi language m