Difference between having and where clause

    1 Votes

Where clause can be applied to a single row, where as Having clause is applied to the whole group. But, it does not mean that we cannot have both Where and Having clause in a single query. If both where and having clauses are used together in single a query, where clause will apply filter condition to the list of records and it will filter the group of records from the table and Having clause will be applied to this resultant group and groups which meets the condition given in having clause will be filtered in the resultant table.

Having clause is used, when we want to use select query to filter data from a table based on a aggregate condition. Aggregate functions used along with the having clause are Sum, Count, Max and Min. So, Having clause should always be used along with group by clause in SQL. If it is not used with Group By clause it throws an error saying – “Column 'Your column' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause“.

Example for Where Clause and Having Clause

Having clause is been added in SQL because WHERE clause could not be used like an aggregate function. Let’s create a multiple tables for demonstrating this with an example –
Table Name - Customer Table
Customer IDCustomer NameCustomer DOBCustomer Age
1 John 10/10/90 24
2 Martin 05/12/65 49
3 Darren 07/26/86 28
4 Derek 01/20/90 25

Table Name - Order Table
Order IDCustomer IDOrder Date
100 1 03/20/15
101 1 03/22/15
102 2 01/29/14
103 3 06/22/14
104 3 08/24/14
105 3 01/19/15
Now let’s filter the list of orders of the customer – “John” done –
SELECT o. Order ID, o. Order Date FROM Customer as c INNER JOIN Order as o ON c.Customer ID = o.Customer ID WHERE c.Customer Name = ‘John’

 

Above is the straight forward query to filter all the orders done by the customer – “John” and displaying the order date and order ID of the filtered records. So here in the above query we can use Group by clause and having clause also along with the existing where clause as shown below –

So below query is used to filter the orders of all the customers and group all the orders of the customers and filter the customers who has more than one order using having clause.

SELECT o.CustomerID FROM Customer as c INNER JOIN Order as o ON c.Custome rID = o.Customer ID GROUP BY o.Customer ID HAVING COUNT(o.Customer ID) > 1

So as we can see in the above query I am trying to check number of occurrence of Customer ID records in Order table, which means the number of orders done by the customers and then I am trying to fetch the Customer ID which is matching the filter criteria in having clause. So in case you want to get the name of the customer instead of customer ID then below query can be used –

SELECT c1.Customer Name
FROM Customer as c1 INNER JOIN
(
SELECT o.Customer ID as Customer ID
FROM Customer as c
INNER JOIN Order as o ON c.Customer ID = o.Customer ID
GROUP BY o.Customer ID
HAVING COUNT(o.Customer ID) > 1
)cinner
ON cinner.Customer ID = c1.Customer ID

Looks straight forward query. Now my first query has been moved to inner query and now new join has been introduced to match the Customer ID from the filtered data and Customer table’s Customer ID. So the selected customer ID‘s Customer Name has been fetched. This is not only the way to fetch the Customer Name but this is one way of the way.

We can use Temporary table as well for the same purpose. Group by clause allows to us to use functions like max(), min(), sum() etc. which are very useful where as “where” clause does not work with these functions. So having clause can be used with aggregate functions whereas where clause will not work with aggregate functions.

Let’s discuss one more example with different sets of tables. Let’s create a table called “Employee” with list of columns as shown below –

Table Name - Employee

Employee IDEmployee NameEmployee AgeEmployee Salary
1 Wayne 29 20000
2 Jack 26 15000
3 Danny 28 35000
4 Kieran 21 13500
5 John 36 40000

Now let’s create one more table called – “Department” as shown below –

Table Name - Department

DepartmentEmployee IDDepartment Name
100 1 Dept1
200 1 Dept2
300 3 Dept3
400 4 Dept4
500 5 Dept5

SELECT d. Department ID, d. Department Name
FROM Employee as e
INNER JOIN Department as d ON e.Employee ID = d.Employee ID
WHERE e. Employee Name = ‘John’

Above query is used to fetch the department details of employee named “John”. And now to filter all the employees which belongs to multiple departments.

SELECT d. Employee ID
FROM Employee as c
INNER JOIN Department as d ON e.Employee ID = d.Employee ID
GROUP BY d. Employee ID
HAVING COUNT(d. Employee ID) > 1

Popular Videos

communication

How to improve your Interview, Salary Negotiation, Communication & Presentation Skills.

Got a tip or Question?
Let us know

Related Articles

RDBMS and DBMS Introduction, Concepts and Basics
Primary Key, Foreign Key and Unique Key
SQL Create Table, DROP Table Syntax and SQL Default Command
SQL Select with Order By, Insert, Update and Delete Command
SQL Operators - Where, IN, AND, OR and Between
SQL Like and Distinct Command
SQL Join Command - Inner, Outer and Left Join
SQL Mathematical Functions, Concat and Trim Commands
SQL Group By and Having Command
Difference between Inner join and Left join
Clustered Index vs Non Clustered Index