The difference between Having and Where clause is one of the most commonly asked database interview questions. The most answer by many aspiring developers is Where clause is used to select any queries while Having clause is used in queries containing aggregate function. Both definitions are correct but when they are asked to explain the select query result most developers will get confused. The main difference is Where clause is used to fetch all the data from records in a table and Having clause is used to summarize the data fetched by where clause.

• Read: All previous interview questions

Example for Where and Having Clause

In our example we have two tables named Player and Barcelona. The Player table will contains the id, name, age and salary of the players. The Barcelona table will contain the various roles played by the players. To find the roles played by the players both the table are combined using barce_id. The created tables will resemble the tables given below:

SELECT * FROM Player;

SELECT * FROM Barcelona;

SELECT d.roll_playd, count(e1.plyer_name1) as NUM_PLYER1, avg(e.plyer_salary1) as

AVG_SALARY1 FROM Player e1,

Barcel d WHERE e1.barce_id=d.barce_id AND plyer_salary1 > 147,000 GROUP BY

d.roll_playd;

SELECT d.roll_playd, count(e.plyer_name1) as NUM_PLYER1, avg(e.plyer_salary1) as

AVG_SALARY FROM Player e1,

Barcel d WHERE e1.barce_id=d.barce_id AND plyer_salary1 > 147,000 GROUP BY

d.roll_playd HAVING AVG_SALARY > 147,000;

From the above table two forward players with an average salary of 165,000, one midfielder and defender with average salaries 180,000 and 127,000 are displayed. According to the example Where clause is used to filter rows and aggregate them. Having clause is used in final filtering process in which players whose salary is less than 125,000 will not be displayed.

Types of Indexes in SQL

Difference between Where and Having clauses

• Apart from the query select, Where clause uses queries like UPDATE and DELETE. Having clause only has SELECT query. In the following example Where clause will work while Having clause will result in a error.

update Barcel set roll_playd=“Goal keeper” WHERE barce_id= 04; // works good

update Barcel set roll_playd=“Goal keeper” HAVING barce_id= 04; // It’s an error

Incorrect syntax near the word ‘HAVING’ : update Barcel set roll_playd=’Goal keeper’ HAVING barce_id=1

• The Clause WHERE is used to filter rows. WHERE clause is applied to all the rows present in a table.
• HAVING clause is used to filter the groups present in SQL.
• WHERE clause is used before GROUP BY clause while HAVING clause is used after GROUP BY clause.
• In SELECT query to perform aggregate function both WHERE and HAVING clauses are used together. In SELECT query, WHERE clause is applied to individual rows. The rows which passed the SELECT query are passed into HAVING clause to filter the formed groups.
• WHERE clause can be used in static non-aggregated columns while HAVING clauses are used only in aggregated columns.
• When GROUP BY clauses are not used; HAVING clause behaves like a WHERE clause.