Describe Where vs Having

Question

Difference between Where and Having

Amit Mohanty · Answer

In SQL Server, the WHERE and HAVING clauses are both used to filter data, but they are used in different contexts and operate at different stages of the query processing. WHERE: It is used to filter rows before any grouping or aggregation is performed. It is applied to individual rows in the table and can be used in SELECT, UPDATE, DELETE etc. It filters data at an earlier stage, right after the data is fetched from the table. We can't use aggregate functions directly. HAVING: It is used to filter groups of rows after grouping and aggregation have been performed. It is applied to the result of the GROUP BY clause and only used with the GROUP BY clause or with aggregate functions. It filters data at a later stage, after the GROUP BY clause has formed groups and aggregate functions have been applied. We can use aggregate functions to filter. Consider a table Sales with columns SaleID, ProductID, Quantity, and SaleDate. Using WHERE: SELECT ProductID, Quantity FROM Sales WHERE Quantity > 10; This query selects rows where the Quantity is greater than 10 before any aggregation or grouping. Using HAVING: SELECT ProductID, SUM(Quantity) AS TotalQuantity FROM Sales GROUP BY ProductID HAVING SUM(Quantity) > 100; This query groups the rows by ProductID, calculates the total quantity for each product, and then filters those groups to include only products with a total quantity greater than 100.

Naveen Kumar · Answer

The WHERE clause filters individual records before any grouping or aggregation occurs and operates at the record level meaning it checks conditions for each row in the table. The HAVING clause filters results after grouping or aggregation and It operates at the group of records level, applying conditions to aggregated data.

Jaydeep Patil · Answer

the WHERE clause is used to filter records before any groupings are made, whereas the HAVING clause is used to filter records after the groupings have been made. Essentially, WHERE is used with individual records, and HAVING is used with aggregated data. Where: SELECT * FROM Employees WHERE Department = 'Sales'; Having: SELECT Department, COUNT(*) as EmployeeCount FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

Riddhi Valecha · Answer

Hello, Consider the following table - Roll_no S_Name Age 1 a 17 2 b 20 3 c 21 4 d 18 5 e 20 6 f 17 7 g 21 8 h 17 WHERE CLAUSE - SELECT S_Name, Age FROM Student WHERE Age >=18 Output - S_Name Age b 20 c 21 d 18 e 20 g 21 HAVING CLAUSE - SELECT Age, COUNT(Roll_No) AS No_of_Students FROM Student GROUP BY Age HAVING COUNT(Roll_No) > 1 Output - Age No_of_Students 17 3 20 2 21 2 Hope this helps.