How to use WHERE and HAVING clause in SQL? Example Tutorial

Hello guys, if you want to learn about WHERE and HAVING clause in SQL then you have come to the right place. This blog shall be explaining how to use WHERE and HAVING clauses in SQL. but it won't be fine if we just jump to it. We need a good understanding of what SQL is and what it is used for. SQL stands for structured queried language, as it sounds it is a language or syntax that is used to query the database.

What is a Database?

A database is a collection of structured information, or data, typically stored electronically in a computer system. A database is controlled by a database management system Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often A database is a collection of related records.

It is organized in such a way that when data is queried, it's easily accessed. Database is designed in such a way that it has a table(s) where different kinds of information are stored. The table(s) has rows and columns, it has indexes too which makes it easier to find a piece of particular information. There are four common operations you can carry out in the database, popularly known as CRUD.

C - create

R- read

U-update

D- delete

Create - you can create a table in the database e.g Employee Table, Student Table, etc. The table you created would have attributes that are peculiar to it e.g, id, name, age, gender, etc. Id is a primary key for all tables because it's always unique, what do I mean? even if we have two people of the same name, their id can never and will never be the same. 

Think of it that you need to access one of the person through the name. it would bring the two people. the best way to handle that is to access it through the primary key, which is the id, reason being that it is unique to a person. 

Read - This operation means that you are selecting or getting something from the database. It could be called retrieve too. just think about it that you are querying what is in existence in the database. i.e what you have created.

Update - It is simple and direct as it sounds, You are updating a new feature or change to whatever you have created in your database.

Delete - When you remove something, so far it's not a necessity any longer. assuming you made a mistake and now you have gotten the right thing then the previous kinds of stuff you were working with have to be deleted since it's of no use.

I hope this overview of the basic concepts of the database gives a background understanding of what we are about to do. 

Fig 1.0 HAVING Clause VS WHERE Clause

So, our focuses are WHERE and HAVING clauses in SQL. Now I shall be teaching you how to use these clauses and why we use them.

Let us first start by creating a mental picture in our heads. STUDENT TABLE of a school

student Table has the following columns: id, name, gender, class, course of  Study (Let us keep it simple!)

STUDENT TABLE

+-------------------------------------------------------+--+--+--+--+
| id name gender level course                           |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 1. 001 Wilms cole male Year 1 Business Admin          |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 2. 002 Alfred Toni female Year 3 Software Engineering |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 3. 003 Grace Rolli female Year 5 Medicine             |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 4. 004 Cyndi Lo female year 2 Theatre art             |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 5. 005 John Smith male year 3 Medicine                |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+
| 6. 006 Chukwu Dan male year 4 Education psycology     |  |  |  |  |
+-------------------------------------------------------+--+--+--+--+

So right now we are to perform some queries on this table most importantly showing you how to use the two clauses talked about  and why it is been used.

1. SELECT name
2. FROM student
3. WHERE gender = female
4. HAVING COUNT (id ) > 002

The query above is saying that we should select the name column from student table where gender is female. That is the first condition. It gives us name of females from the student table. And females whose id are greater than 002. 

So , even though Alfred Toni is a female, she is not going to be part because her id is 002. we only want females that their ids are greater than 002 (line 4). I hope that explains how where clause works. 

 

For "Having" clause: it is used on aggregate functions, What are aggregate functions ? they are mathematical functions. The commonly used ones are: SUM,COUNT,AVERAGE,MAX,MIN etc. 

So you use "Having" clause when you want to perform any of the mathematical function above. Because Using "Where" clause to perform mathematical functions can not work, That is not what it does.

 

In line 4, you can see the mathematical function "count" there. the condition is that it counts the id and gives us names of those their ids  are greater than 002.

The results are : Cyndi Lo and Grace Rolli because they are the only females that meets the second requirement.

Other SQL and Database Articles you may like:

• 4 Examples of CASE expression in SQL Server
• How to use Stored Procedure in SQL?
• Difference between Self and Equi join in SQL
• Difference between UNION and UNION ALL in SQL
• What are T-SQL Window Functions?
• MySQL vs NoSQL comparison
• 10 Examples of ALTER command in SQL
• How to find top 10 records in a table?

Thanks for reading this article so far. IF you like it then please share and if you have any questions, feel free to ask.