WHERE to Filter Data

1. Getting Started

The WHERE clause is one of the most useful clauses to know when working with a SELECT statement. WHERE is used to filter records according to specified criteria. The WHERE clause is optional and adds conditional restrictions to the SELECT statement that will help limit the result set so that the user is not overwhelmed with data that is hard to read or understand.

WHERE displays only the records that fit the condition listed in the WHERE clause. By using the WHERE clause, you can easily answer questions like:

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

 
SELECT *
FROM customer
WHERE customer_id = 5; 

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

term to know

WHERE Clause

A clause that filters records in a SELECT statement.

2. Filtering Strings

SQL requires single quotes around text values. Numeric values should not be enclosed in quotes. Here is an example of what would happen if we forgot to include quotes around the text value 'Helena':

SELECT *
FROM customer
WHERE first_name = Helena;

We would get an error message:

This is because the database thinks the text value is a column. This could also present a problem if the text value is also an actual column. You would not get an error message; however, the results would not be what you wanted, either.

To properly use the WHERE clause, you would use the single quotes around the text values:

SELECT *
FROM customer
WHERE first_name = 'Helena';

3. Comparison Operators

We looked at the = operator above, but there are many other operators that can be used in the WHERE clause. Other comparison operators include:

For example, let's find the invoices that have a total greater than 14.

SELECT *
FROM invoice 
WHERE total > 14; 

The result set includes 12 rows. If we change the WHERE clause to >= 14 (greater than or equal to 14) and include all invoices with the value of 14, 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, not all numbers are integers. Many are percentages like 25% or decimals like 14.5. The WHERE clause handles data like this differently depending on the column's data type. For example, if its type is decimal, that means that numbers like 12.4 and .25 are stored in that column.

If you had decimal numbers and used SELECT * FROM invoice WHERE total > 14, the query would return all numbers larger than 14, so 14.091 is larger and would be in the data set.

The data type for your column matters, and it is a good thing to know when you are working with databases and the data in them.

watch

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 WHERE clauses.