WHERE to Filter Data

1. Getting Started

The WHERE clause is used to filter records in a SELECT statement. It only displays the records that fit the condition listed in the WHERE clause. You will remember in the prior tutorial that you had to scroll through the data to find the necessary information after it has been sorted. In looking at the tables that we have, we may have questions like:

• Which invoices have a total greater than 14?
• Which customers live in Canada?
• Which employees report to the General Manager?

As you’ve seen so far with the SELECT statement, the WHERE clause is optional. With the WHERE clause, we will add the conditional restrictions to the SELECT statement that will help limit the result set.

If we wanted to find the customer information of the customer_id that was equal to 5, we would run it as:

SELECT *
FROM customer
WHERE customer_id = 5;

You will notice in the WHERE clause, we define the column (customer_id), the comparison operator (=), and the value that we wanted to compare it to (5).

If there are no rows that match the criteria in the WHERE clause, you should see a message similar to the following:

SELECT *
FROM customer
WHERE customer_id = 1000;

---

2. Filtering Strings

Note that SQL requires single quotes around text values. Numeric values should not be enclosed in quotes. For example, consider if we forgot to include quotes around the text values such as in this statement:

SELECT *
FROM customer
WHERE first_name = Helena;

In this case, we would get an error:

This is because the database thinks the text value is a column. This could present a problem if the text value is an actual column as you would not get an error such as what is listed above. However, the results would not be what you’d expect to see either.

To properly use the WHERE clause on text-based columns, you would use the single quotes around the text:

SELECT *
FROM customer
WHERE first_name = 'Helena';

---

3. Comparison Operators

We have only looked at the = operator above, but there are many other operators that can be used. The comparison operators include:

= means equal to
< means less than
<= means less than or equal to
> means greater than
>= means greater than or equal to
<> means not equal to

Let us find the invoices that have a total greater than 14.

SELECT *
FROM invoice
WHERE total > 14;

We can include all invoices with the value of 14 by adding an equal sign; the result set goes from 12 rows to 61 rows returned.

SELECT *
FROM invoice
WHERE total >= 14;

When it comes to integer values being compared, there would be no difference between using these two statements:

SELECT *
FROM invoice
WHERE total >= 15;

or

SELECT *
FROM invoice
WHERE total > 14;

However, if the total contained decimal values, these two statements would not be equivalent. The second statement would return rows with a total value between 14 and 15, like 14.5, whereas the first one would not.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.