Difference between having and where clause

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 BYclause“.

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 ID	Customer Name	Customer DOB	Customer 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 ID	Customer ID	Order 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 ID	Employee Name	Employee Age	Employee 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

Department	Employee ID	Department 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