Online College Courses for Credit

+
1 Tutorials that teach WHERE to Filter Data
Take your pick:
WHERE to Filter Data

WHERE to Filter Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses the WHERE clause to filter data from a table.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the WHERE clause within a SELECT statement to filter data in the result set in three parts:

  1. Getting Started
  2. Filtering Strings
  3. Comparison Operators

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;

File:11195-1130-1.png

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;

File:11196-1130-2.png


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:

File:11197-1130-3.png

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

File:11198-1130-4.png


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;

File:11199-1130-5.png

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;

File:11200-1130-6.png

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.

summary
Filtering data using the WHERE clause in SELECT statements can help limit the amount of data being returned based on the conditions listed. There are multiple comparison operators that can be used to filter data.