Comparison of Different SQL Clauses

Anushkakhatri 03 Jun, 2022 • 5 min read

This article was published as a part of the Data Science Blogathon.

Introduction to SQL Clauses

SQL clauses like HAVING and WHERE both serve to filter data based on a set of conditions. The difference between the functionality of HAVING and WHERE  as SQL clauses are generally asked for in SQL interview questions. In this article, we will explain the functionalities and differences between the HAVING and WHERE clauses using an example.

HAVING Clause 

SQL HAVING clause fetches the necessary records from the aggregated rows or groups on the basis of the given condition. It is generally used along with the GROUP BY clause and applies to the column operations. It operates on the aggregate functions such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’. We can use the HAVING clause only with SELECT statements. It cannot be used with UPDATE or DELETE statements. The syntax is as follows:

Syntax of the HAVING Clause with the SELECT statement:

 SELECT column_1, column_2, column_3, column_N

FROM Table_Name

WHERE condition

GROUP BY column_1, column_N

HAVING condition 

ORDER BY column_1, column_2, column_N;

WHERE Clause

SQL WHERE clause fetches the necessary records from a single table or multiple tables that meet the given condition. The WHERE clause can function without the GROUP BY clause and can perform row operations. It is used with single row functions like character functions, general functions, case conversion functions, date functions, or number functions. We can use the WHERE clause with any SELECT, UPDATE, and DELETE statement. The syntax is as follows:

a) Syntax of the WHERE Clause with the SELECT statement:

SELECT column_1, column_2, column_3, column_N

FROM Table_Name

WHERE condition;

b) Syntax of WHERE Clause with the UPDATE statement:

UPDATE Table_Name

SET column_1=value_1, column_2=value_2, column_3=value_3, column_N=value_N

WHERE condition;

c) Syntax of WHERE Clause with the DELETE statement:

DELETE FROM Table_Name WHERE condition;

 

Examples of HAVING and WHERE Clause Functionality

In the following example, we will demonstrate the functionality of the HAVING and WHERE clause:

Let’s start by creating a database called Student:

>> CREATE database Student;

SQL Clauses

Use the Student database:

>> USE Student;

SQL Clauses

Let’s create the Student_Score table with Student_ID as the primary key:

>> CREATE TABLE Student_Score(

Student_ID INT NOT NULL PRIMARY KEY,

Student_Name varchar(20),

Gender varchar(20),

Math_Score INT,

Bio_Score INT);

SQL Clauses

Using the SHOW TABLES statement, you can view the tables in the current database:

SQL Clauses

Insert the values into the Employee_detail table, then use the SELECT command to view the contents:

>> INSERT INTO Student_Score(Student_ID, Student_Name, Gender, Math_Score, Bio_Score)

VALUES(1001, ‘Tom Ford’, ‘Male’, 68, 90),

(1002, ‘Ananya Verma’, ‘Female’, 97, 86),

(1003, ‘Eva Jackson’, ‘Female’, 86, 72),

(1004, ‘John Smith’, ‘Male’, 65, 91),

(1005, ‘Tanvi Sharma’, ‘Female’, 89, 63),

(1006, ‘Lilly Mathew’, ‘Female’, 74, 82);

>> SELECT * FROM Student_Score;

 

SQL Clauses

HAVING clause with SELECT statement:

Our goal is to find out the average Math_Score of students who are gender grouped and have an average Math_Score greater than 60 and arranged in descending order.

>> SELECT Gender, AVG(Math_Score) as avg_math_score

FROM Student_Score

GROUP BY  Gender

HAVING AVG(Math_Score)>60

ORDER BY AVG(Math_Score) DESC;

 

MySQL

 

WHERE clause with SELECT statement:

In your example, we would like to know the Student_ID, Student_Name, Gender, and Bio_Score of the students who score more than 80.

>> SELECT Student_ID, Student_Name, Gender, Bio_Score

 FROM Student_Score

WHERE  Bio_Score >  80;

WHERE clause

 

WHERE clause with an UPDATE statement:

For the student with Student_ID 1004, we want to update the Math_Score column to 65 and the Bio_Score column to 95.

>> UPDATE Student_Score

SET Math_Score=70, Bio_Score=95

WHERE Student_ID=1004;

SQL Clauses

We can use the SELECT statement to view the updated Student_Score table:

>> SELECT * FROM Student_Score;

MySQL

WHERE clause with DELETE statement:

We want to delete the records where the Gender of a student is ‘Male’:

>> DELETE FROM Student_Score

WHERE Gender=’Male’;

SQL Clauses

We can use the SELECT statement to view the filtered record:

>> SELECT * FROM Student_Score;

Having clause and where clause

Difference Between HAVING and WHERE Clause

  HAVING WHERE
1. HAVING clause enables you
to fetch necessary records from the aggregated rows or groups on the basis of the specified condition.
WHERE  clause enables you to fetch necessary records from the table on the basis of the specified condition.
2. HAVING clause should be used along with the GROUP BY clause and is used after the GROUP BY clause. It is possible for the WHERE clause to function without the GROUP BY clause and with the GROUP BY Clause, it’s been used before the GROUP BY Clause.
3. HAVING Clause applies to the column operations. WHERE Clause applies to the row operations.
4. Aggregate functions can be used in the HAVING Clause Aggregate functions cannot be used in the WHERE Clause
5. HAVING Clause is also known as a post-filter since it selects rows after aggregate calculations have been carried out. WHERE Clause is also known as a Pre-filter as it selects the rows before aggregation calculations are
conducted.
6. HAVING Clause can only be used with ‘SELECT’ statements, but not with ‘UPDATE’ or ‘DELETE’ statements. WHERE Clause can be used with the ‘SELECT’, ‘UPDATE’, and ‘DELETE’ statements.
7. HAVING clause can be used with multiple row functions, such as ‘SUM’, ‘COUNT’, ‘MAX’, ‘MIN’, or ‘AVG’.
WHERE Clause can be used with a single row function such as character, general, case conversion, date, or number functions such as UPPER, LOWER, REPLACE, etc.

Conclusion

This article summarized how both HAVING and WHERE clauses work, as well as how they differ. We have used the Student_Score table as an example and performed various operations with HAVING and WHERE clauses. Hopefully, this article will help you better understand how HAVING and WHERE clauses work, as well as how to differentiate them. Feel free to comment below if you have any queries or connect with me on LinkedIn.

I hope you liked my article on SQL clauses? Share in the comments below.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Anushkakhatri 03 Jun 2022

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear

Janardhan
Janardhan 02 Sep, 2022

Explained very well. However I would like to know bit clarity on my points, could you please help? 1. Why where clause fails to filter aggregate functions? 2. Can we use group by clause without Agreegate functions? Regards, Janardhan

Shailaja Guguloth
Shailaja Guguloth 06 Nov, 2022

I interested to learn the SQL class