SQL: WHERE vs HAVING Clause
🧵👇
🧵👇
1. Example
- To understand the diffference let's consider below table "EMPLOYEE_TABLE" with following results
Eg:
- To understand the diffference let's consider below table "EMPLOYEE_TABLE" with following results
Eg:
2. Without GROUP BY
- As shown below, the 1st query is using WHERE and 2nd is using the HAVING clause
- Both queries will give the same results, but 1st query will use indexing on column EMP_ID (if exist) but 2nd query, will simply scan the table to get results
- As shown below, the 1st query is using WHERE and 2nd is using the HAVING clause
- Both queries will give the same results, but 1st query will use indexing on column EMP_ID (if exist) but 2nd query, will simply scan the table to get results
3. With GROUP BY
- If HAVING is used in GROUP BY query, then it will filter the aggregated results at the end, after GROUP BY is executed
- While WHEN will be executed before GROUP BY, it will filter the record before GROUP BY
- If HAVING is used in GROUP BY query, then it will filter the aggregated results at the end, after GROUP BY is executed
- While WHEN will be executed before GROUP BY, it will filter the record before GROUP BY
- Below shown a simple GROUP BY query which is calculating avg salary of employees based on the city
- Initially without using WHERE and HAVING it is showing all city records with avg salary
Eg:
- Initially without using WHERE and HAVING it is showing all city records with avg salary
Eg:
- Now, let's apply WHERE and HAVING, this time it will filter out Gotham city record and New york city record
- Gotham city record will be removed due to the WHERE clause
- New york city record will be removed due to the HAVING clause as it has an average salary < 4000
Eg:
- Gotham city record will be removed due to the WHERE clause
- New york city record will be removed due to the HAVING clause as it has an average salary < 4000
Eg:
4. Conclusion
- The HAVING clause should be only used with the GROUP BY clause, other than that we should always use the WHERE clause for filtering, as it uses Indexing
- WHERE is always executed before GROUP BY, and HAVING is executed afterwards
- The HAVING clause should be only used with the GROUP BY clause, other than that we should always use the WHERE clause for filtering, as it uses Indexing
- WHERE is always executed before GROUP BY, and HAVING is executed afterwards
Thanks for staying till the end!
Before you go, do you know?
I've also started writing on LinkedIn.
I share some exclusive content there, which I never share here.
Follow me on Linkedin to stay updated with Backend content:
linkedin.com
Before you go, do you know?
I've also started writing on LinkedIn.
I share some exclusive content there, which I never share here.
Follow me on Linkedin to stay updated with Backend content:
linkedin.com
Namaste, I'm Vikas!
Every Mon, Wed & Friday - I tweet a thread on Backend Development. Follow me @vikasrajputin to read all my future threads.
Like and RT the first tweet below to support my work.
Every Mon, Wed & Friday - I tweet a thread on Backend Development. Follow me @vikasrajputin to read all my future threads.
Like and RT the first tweet below to support my work.
جاري تحميل الاقتراحات...