SQL queries use the HAVING and WHERE clauses to filter the data. However, the WHERE clause cannot be used with the aggregate functions, which is where the HAVING clause comes into the picture! In this tutorial, we will learn about filtering data with HAVING and WHERE clause and their uses and differences between them with the help of practical examples.
Overview of HAVING Clause
The HAVING clause in SQL is used to filter the results returned by the GROUP BY clause based on some specified criteria. It is generally used with the GROUP BY clause. The HAVING clause is kinda similar to the WHERE clause since both of them are used to filter out records. However, the HAVING clause can be used with aggregate functions(COUNT, MAX, SUM) whereas WHERE cannot.
Syntax
SELECT column01, column02
FROM table01
WHERE condition
GROUP BY column01
HAVING condition
ORDER BY column02Code language: SQL (Structured Query Language) (sql)Example
Consider the below table named, EmployeeInfo:
Let’s say we want to fetch the record of the types of Jobs with the total sum of salary which is less than 77000 from EmployeeInfo table.
SELECT Job, SUM(Salary)
FROM EmployeeInfo
GROUP BY Job
HAVING SUM(Salary) < 77000;Code language: SQL (Structured Query Language) (sql)
The above query selects the job titles and the total salary from each job from the EmployeeInfo table. Then, it groups the results by job title and filters out the groups to include only those whose total salary is less than 77000.
Overview of WHERE Clause
The WHERE clause in SQL is a component that is used to filter records from a database table which is based on some specific conditions. It works on individual rows of data by selecting only those that meet the specified criteria. Unlike the HAVING clause, which filters aggregated results, the WHERE clause focuses on filtering raw data before any aggregation takes place.
Syntax
SELECT column01, column02
FROM table01
WHERE conditions;Code language: SQL (Structured Query Language) (sql)Example
Let’s say we want to fetch the record of employees with a salary greater than 50000 from the EmployeeInfo table.
SELECT Ename, Job,Salary
FROM EmployeeInfo
WHERE Salary > 50000;Code language: SQL (Structured Query Language) (sql)
Filtering Rows with WHERE and HAVING Clause
If we want to apply conditions before and after aggregation, the WHERE and HAVING clauses allow users to do this task effectively.
Let’s say we want to fetch the records for departments where the total salary is greater than 50,000 and the average age of employees who work in that department is less than 50 in the EmployeeInfo table.
SELECT DeptNo, SUM(SALARY) AS total_salary, AVG(Age) as average_age
FROM EmployeeInfo
WHERE Salary > 50000
GROUP BY DeptNo
HAVING AVG(Age) < 40;Code language: SQL (Structured Query Language) (sql)
Filtering Rows with Multiple Values
If we want to filter out rows with more than one aggregate value, the HAVING clause offers a flexible approach to do this task effectively.
Let’s say we want to fetch the record of the department along with total salary and average age, but for only those departments where the total salary is more than 100000 and the average age is below 35 in the EmployeeInfo table.
SELECT DeptNo, SUM(SALARY) AS total_salary, AVG(Age) as average_age
FROM EmployeeInfo
GROUP BY DeptNo
HAVING SUM(SALARY) > 100000 AND AVG(Age) < 35;
Code language: SQL (Structured Query Language) (sql)
Also Read: Difference between JOIN and Multiple Tables in FROM
HAVING vs WHERE Clause
The HAVING clause uses the GROUP BY clause to filter individual rows after the aggregation. It determines whether a group’s aggregate value satisfies a particular condition. The HAVING clause can only be used with a SELECT statement.
Whereas, the WHERE clause filters individual rows before any aggregation. It works on the original data set and selects rows that meet the specified condition based on column values or logical expressions. The WHERE clause can be used with SELECT, UPDATE, DELETE and INSERT at the row level.
Conclusion
In this tutorial, we have learned about filtering the data with the HAVING and WHERE clause and their usage with the help of practical examples. Also, we have seen how is it different from the WHERE clause. The SQL HAVING and WHERE statement is an effective way to improve query performance in terms of data analysis, business intelligence and reporting. Knowledge of this section allows SQL users to gain important details from aggregated data. We hope you enjoyed it.