Where v/s Having when to use in SQL

We use the SQL queries to fetch the data from a bucket, here bucket is imaginary.. I used this term to simplify the technical words like cloud, database, dataset, excel sheet or any other form of storage. But to get the exact data that we need, we should use some filters that will avoid null values, non relatable data or only the desired result.

Such filters are known as "Having" clause or "WHERE" clause.

The difference between "Where" and "Having" clause is as follows:

WHERE Clause with Example:

• The WHERE clause is applied as a row operation.

• We can not use aggregate functions like Count, Sum, Avg, Min, Max with WHERE clause.

• It uses the comparison operators (eg: =, <, >, LIKE, <>) to compare values to constants, variables, expressions.

• You can combine multiple conditions using logical operators (eg: AND, OR, NOT) to create more complex filtering logic.

• Multiple WHERE clauses: While not common, some databases allow using multiple WHERE clauses using logical operators (eg: WHERE condition1 AND condition2)

Syntax:

SELECT column_name(s) FROM table_name WHERE condition();

Let's see a simple example of WHERE clause from DVDRental database.

HAVING Clause with Example:

• The HAVING clause is used to apply a filter on the result of GROUP BY based on the specified condition.

• Having clause is used to filter data according to the conditions provided.

• Having a clause is generally used in reports of large data.

• Having clause is only used with the SELECT clause.

• In the query, ORDER BY is to be placed after the HAVING clause.

• HAVING Clause is implemented in column operation.

• Having clause is generally used after GROUP BY.  

 Syntax :

          SELECT column_name()

          FROM  table_name

          WHERE condition()

        GROUP BY column_name()

           HAVING condition()                        

Let's see the example of using HAVING clause in a SQL query:

Additionally, in simple words, SQL structure for more complex queries is:

• SELECT  Column we want to look at 

• FROM  Table the data lives in

• WHERE  Certain condition on the data

• GROUP BY  Column we want to aggregate by

• HAVING  Certain condition on the aggregation

• ORDER BY  Column we want to order results by and in ASC or DESC order

• LIMIT The maximum number of rows we want our results to contain Hope this article helped you in understanding the basic difference between WHERE and HAVING clause. Thank you for reading my blog!