Where and having clause example in SQL

In my previous articles i have given the examples of different SQL concepts. In this article I would like to explain about the Difference between Where and Having in SQL with some real world industry examples. I would like to give some most important Where and having clause example in SQL so that user will get the idea about both the clauses. The user will confuse in using where clause and having clause in different types of scenarios in SQL. Sometimes there is need to use the where clause and sometimes there is need to use the having clause. So that there is need to understand the difference between where and having clause with multiple examples.

Where and having clause example | Where vs Having :

  In this section i would like to explain Difference Between Where and Having clause in detail with different real life examples.Both Where and Having clause will perform similar functions but it differs the purpose.While working with different functionalities of SQL user needs to perform different operations which contains where clause as well as having clause.Both Where and having clause is used for filtering purpose but the main difference between Where and Having clause is user can use where clause with any select statement and having clause will be used with select statement with aggregations.User needs to use how the select statement is executed step-wise. User can use where clause with having clause but can not use the having clause without where clause.I will give you the difference between Where and having in tabular format in next section.But for now i will explain the Where clause and having clause with its syntax and examples.

Before checking the syntax of where and having i would like to explain the basic syntax of select statement.

Select Column_names…..

From Table_name

Where Condition

Group by column_name

Having Condition; 

The basic syntax of Select statement is given as above.User can use the where clause and having clause together.

Where clause with examples :

Where clause is most important clause in SQL.There are 90% of queries in SQL uses where condition to filter the records from the table.The where condition is mainly used to filter the records from SQL table or SQL View.

Syntax :

Select Column_name1….Column_nameN From TableName where column_condition

The where clause is used to Filter the table values.User can use where clause with any select statement.

Real Life Example :

Kindly count the number of Students where marks greater than 90.

Here user needs to use the Where condition rather than using where with group by and having clause. 

The above statement will fetch the records of Student table where Marks greater than 90.This is simple example of where clause.I would like to give you another example of where clause which is always helpful in reporting purpose.

Example 2 :

Kindly give me the list of students where date of admission between 1stJune 2018 to 1July 2018.

Select Student_name,Marks from Student where Admission_date >= ’01-June-2018′ and Admission_date <=’01-July-2018′;

The above statement will give you information about student where Admission date between 1st June 2018 to 1st July 2018.

Example 3: Where clause for Pattern Matching

The where clause is useful in pattern matching.The Where clause is always used with Like Wildcard keyword in SQL.

Kindly Give me information about Student where name contains ‘Ami’ String.

Select * from Student Where Student_name Like ‘%Ami%’;

These are some usages of Where clause in SQL.

Having Clause with Examples :

The having clause is always used to filter the values with group by clause.Having clause is always used in select statement where aggregate functions are using.

Real Life Examples :

Kindly count the number of Students where  department is maths.

Select count(Student_name) from Student 

group by Department

having department_name=’Maths’;

The above statement will count the Student where department name is Maths.

Difference Between Where and Having Clause Bullet-Points :

In this section i would like to give you bullet-points of difference between Where and having clause.

Point 1 :

The Where clause is used to fetch the results directly from the table.

The Having clause will fetch the data from table where result-set is processed  and it will filter the summarized data or grouped data.

Point 2 :

User can use the where clause with update and delete statements.

User can not use having clause in update and delete statements.Having clause is always used with select statement.

Point 3 :

Where clause will filter the rows and it will process each and every row.

Having clause will process the bunch of rows or groups in SQL.

Point 4 :

Where clause is used before group by clause.

Having clause is used after group by clause.

Point 5 :

Where clause is used with Having clause when there is need of aggregation.

Where clause alone is used when the aggregation is not needed.

These are the most important bullet point s of Difference between Where and Having clause.I hope you will get idea about difference between where and having.If you have any questions with the same kindly comment in comments section.