Comparison of Different SQL Clauses

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;

Use the Student database:

>> USE Student;

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);

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

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;

 

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;

 

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 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;

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

>> SELECT * FROM Student_Score;

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’;

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

>> SELECT * FROM Student_Score;

Difference Between HAVING and WHERE Clause

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

Passionate about solving business problems with data-driven solutions. Skilled in the field of Data Science and Big Data Engineering.
I have worked on various projects, including developing predictive models, analyzing complex data sets, and designing and implementing data architectures and pipelines.
I enjoy exploring new data science and data engineering techniques and keeping up with the latest industry trends.