Difference between WHERE vs HAVING in SQL

1) WHERE clause is processed right after FROM clause in the logical order of query processing, which means it is processed before GROUP BY clause while HAVING clause is executed after groups are created.

2) If used in GROUP BY, You can refer any column from a table in WHERE clause but you can only use columns which are not grouped or aggregated.

3) If you use HAVING clause without group by, it can also refer any column but the index will not be used as opposed to WHERE clause. For example, the following have the same result set, however “where” will use the id index and having will do a table scan

select * from table where id = 1
select * from table having id = 1

4) You can use an aggregate function to filter rows with HAVING clause. Because HAVING clause is processed after the rows have been grouped, you can refer to an aggregate function in the logical expression. For example, the following query will display only courses which have more than 10 students :

SELECT Course, COUNT(Course) as NumOfStudent from Training GROUP BY Course HAVING COUNT(COURSE)> 10

5) Another key difference between WHERE and HAVING clause is that WHERE will use Index and HAVING will not, for example following two queries will produce an identical result but WHERE will use Index and HAVING will do a table scan

SELECT * FROM Course WHERE Id = 101;
SELECT * FROM Course HAVING Id = 102;

6) Since WHERE clause is evaluated before groups are formed, it evaluates for per row. On the other hand, the HAVING clause is evaluated after groups are formed hence it evaluates for per group.