SQL: Using The Where and Having Clause

Over the course of the past few posts, I have been writing about how to create, delete, and query in SQL. For this post, we will take a closer look into a section about queries. We will be specifically going over the WHERE and HAVING clause.

The WHERE Clause

The WHERE clause is a statement that filters a table based on certain conditions. The query then will only return the columns and observations that meet the conditions given within the WHERE clause. The basic formula for using the WHERE clause is the following:

SELECT <column_1>,..., <column_n>
FROM <table_name>
WHERE <condition>

A basic example would be if we had a users table which has personal information such as first name, last name, address, city, state, and country. If we wanted to find out which users live in the state of New York, we could set that as a condition.

SELECT first_name, last_name
FROM users
WHERE state = 'New York'

It is also useful to know that the = operator is not the only available operator to use in a WHERE clause. There are:

1. > : greater than
2. < : less than
3. >= : greater than or equal to
4. <= : less than or equal to
5. != or <> : not equal to
6. BETWEEN : used when you are looking for a value between two other values
7. LIKE : used when you are searching for a pattern within the observations
8. IN : used when looking for a values within a series of values.

You can also have multiple conditions by using the AND and OR operators. Thus the formula for multiple conditions is

SELECT <column_1>,..., <column_n>
FROM <table_name>
WHERE <condition_1> AND <condition_2> AND ... AND <condition_n>;

For example, we could be interested in the users from a tristate area.

SELECT first_name, last_name
FROM users
WHERE
    state = 'New York' OR
    state = 'New Jersey' OR
    state = 'Connecticut';

The HAVING Clause

The HAVING clause if very similar to the WHERE clause. The HAVING clause is also statement that filters a table based on certain conditions. So, one may ask what is the purpose of a HAVING clause if it seems that it does the same as a WHERE clause. The main reason for the HAVING clause is that the WHERE clause is not useable with aggregate functions. So, if you need to use the COUNT() , MIN() , MAX() , AVG() , or SUM() functions or the GROUP BY statement, the WHERE clause won’t be able to be used for those types of conditions. Instead, the HAVING clause must be used. The formula for this clause is the following:

SELECT <column_1>,..., <column_n>
FROM <table_name>
WHERE <condition(s)>
GROUP BY <column_name(s)>
HAVING <condition(s);

An Example Using Having

Let’s go over an example where you would use the HAVING clause. You own an online service. For this example we will have a clients table and we will have an orders table. In the clients table, there is the personal information of each client along with a unique client id. In the orders table, there is information on the orders such as the products and numbers ordered and the client who ordered. Now, let us consider you want to send a thank you to the clients who have spent more than a certain amount on orders. What needs to be done to find these top spenders?

The first part to consider is what columns are necessary of the required end result. So we need the name or perhaps username and the email for each of the top spenders.

SELECT username, email FROM clients;

Now this gives us all the usernames and emails from the clients table. We need to know who made what orders. To do that we would need to combine it with the orders table. We can do this by using a JOIN, and because we are only interested in those who have made orders, we can use an INNER JOIN. The two tables would be joined on the primary key from the clients table (id) and the foreign key from the orders table (client_id).

SELECT username, email, order_number, order_cost
FROM clients
INNER JOIN orders
ON clients.id = orders.client_id

The next step to consider is that we want to know how many orders each client has made over time. This can be done by using a GROUP BY statement to group rows by unique clients. Additionally, we would want the sum of all the order costs.

SELECT username, email, SUM(order_cost) as total
FROM clients
INNER JOIN orders
ON clients.id = orders.client_id
GROUP BY clients.id

Finally we need to filter the clients by those who have spent more than lets say $10,000. We can’t use the WHERE clause because were are filtering based on an aggregate function and group by statement. Instead we must use a HAVING clause.

SELECT username, email, SUM(order_cost) as total
FROM clients
INNER JOIN orders
ON clients.id = orders.client_id
GROUP BY clients.id
HAVING total > 10000;

Hurray!! We now have a query which would return the clients who have spent more than $10,000 on our service site.

I hope this post and its examples have helped your understanding of the WHERE and HAVING clause and when one needs to used rather than another. Thanks for reading!

For documentation on the WHERE and HAVING clauses:

MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement