Key Difference – where vs having clause in SQL
Data is important for every organization. Therefore, it is necessary to store the data in an organized way to retrieve them. The data is stored in tables. A database consists of a collection of tables. One common database type is relational databases. In a relational database, the tables are related to each other. For example, the table customer is connected to order table. A Relational Database Management System (RDBMS) is a database management system that is based on relational model. It is used to manage Relational Databases. Some examples of RDBMS are MySQL, MSSQL, and Oracle. The Structured Query Language (SQL) is the language used for manipulating and retrieving data in a relational database. There are various clauses in SQL to perform different tasks. Two of them are where and having. This article discusses the difference between where and having clause in SQL. The key difference between where and having clause in SQL is that where clause is used to filter records before a grouping or an aggregation occurs while having clause is used to filter records after a grouping, or an aggregation occurs.
CONTENTS
1. Overview and Key Difference
2. What is where clause in SQL
3. What is having clause in SQL
4. Similarities Between where and having clause in SQL
5. Side by Side Comparison – where vs having clause in SQL in Tabular Form
6. Summary
What is where clause in SQL?
It helps to retrieve, update or delete a particular set of data from the table according to the given condition. The programmer can use where clause to restrict and fetch only the required data. The query executes only on the records where the condition specified by the where clause is true. It can be used with select, update and delete.
Refer the below student table,
To select the name and age of the student whose student_id is equal to 3, the following SQL query can be used.
select name, age from student where student_id= 3;
It is also possible use operators such as not equal to (! =), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=). To select the student_id and name whose age is not equal to 15, the following SQL query can be used.
select student_id, name from student where age! = 15;
To change the age of the student 2 to 13, the following query can be used.
update student set age = 13 where id = 3;
To delete the record of which the student_id is 4, the following query can be used.
delete from student where student_id = 4;
The and, or operators can be used to combine multiple conditions.
select name from student where student_id=1 and age = 15; query will retrieve the name Ann.
These are some examples of where clause in SQL. If there is a Group By clause, the where clause appears before that.
What is having clause in SQL?
There are functions provided by the SQL language to perform calculation easily. They are known as aggregation functions. The min () is used to find the smallest value of the selected column. The max () is used to find the maximum value of the selected column. The avg () is used to find the average in the column and sum () is used to find the total of the column. Those are some examples of aggregation functions. Refer the below order table,
The programmer can write the SQL query as follows to retrieve the customers whose balance is more than 2000.
select * from order group by customer having sum(balance) > 2000.
This will print the customer records whose summation of the balance is larger than 2000. It will print the records of customers Ann and Alex.
The having clause is used to retrieve the values for the groups that satisfy certain conditions. Therefore, the group that falls to the given condition will appear as the result of this. The having clause appears after the group-by clause. If the group-by clause is not present, then the having clause will work similar to the where clause.
What are the Similarities Between where and having clause in SQL?
- Both are clauses in Structured Query Language.
- Both can be used to filter the retrieve a set of data.
What is the Difference Between where and having clause in SQL?
where vs having clause in SQL |
|
| The where is an SQL clause that is used to specify a condition while fetching the data from a single table or by joining with multiple tables. | The having is an SQL clause that specifies that an SQL select statement should only return rows where aggregate values meet the specified conditions. |
| Purpose | |
| The where clause is used to filter rows. | The having clause is used to filter groups. |
| Aggregation | |
| The where clause cannot be used with aggregation functions unless it is in a subquery contained in a Having clause. | The aggregation functions can be used with the having clause. |
| Filtering Method | |
| The where clause behaves as a pre-filter. | The having clause behaves as a post filter. |
| Group By Clause Order | |
| The where clause is used before the Group By clause. | The having clause is used after the Group By clause. |
| Used With | |
| The where clause can be used with select, update and delete. | The having clause is used only with select. |
Summary – where vs having clause in SQL
The Structured Query Language (SQL) is the language used in relational databases. The where are having are two clauses in SQL. This article discussed the difference between where and having clause. The difference between where and having clause in SQL is that where is used to filter records before a grouping or an aggregation occurs while having is used to filter records after a grouping, or an aggregation occurs.
Reference:
1.“Using the WHERE SQL clause.” WHERE Clause in SQL Statements | Studytonight. Available here
2.Having clause with Group SQL Functions | DBMS Tutorial | Studytonight. Available here
3.“Having (SQL).” Wikipedia, Wikimedia Foundation, 3 Mar. 2018. Available here
4.“Having vs Where Clause?” GeeksforGeeks, 9 May 2017. Available here