Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

WHERE clause vs. HAVING clause

Mohammad Razi ul Haq

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

WHERE clause

The WHERE clause in SQL is used while fetching data from a table. Any row that does not match the condition given in the WHERE clause is not fetched. The clause works on row’s data and not on aggregated data; hence, the WHERE clause is applied to filter data before grouping the data (using GROUP BY) for further processing. However, it is not always necessary to apply the GROUP BY clause after the WHERE clause as the WHERE clause can also be applied alone.

Only rows that meet the filter condition are returned. The condition is checked on each row.

HAVING clause

The HAVING clause in SQL is also used while fetching data from a table. The clause works on aggregated(grouped) data and is evaluated after the grouping is applied to the data. Hence, the HAVING clause is used to filter grouped data after applying the WHERE clause. Unlike the WHERE clause, which can be applied without GROUP BY, the HAVING clause requires the GROUP BY clause before it can be applied to the data. Since the data is grouped, the HAVING clause can contain aggregate functions such as COUNT(), MAX(), and MIN().

Having clause filters GROUPED data.

SQL statements often have both the WHERE and the HAVING clause. The WHERE clause is applied first to filter each row based on a specific condition. The speed of computation is faster since the operations are applied to a much smaller, filtered dataset. The HAVING clause is used to filter the grouped data according to the condition. The performance is considerably less in the HAVING clause since the filtering is applied to the grouped dataneeds to be sorted first, which adds computational cost.

RELATED TAGS

sql
communitycreator

CONTRIBUTOR

Mohammad Razi ul Haq

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring