Filtering Results (The WHERE Clause)

In this lesson, I’m going to introduce you to the WHERE clause.

Till now all of the queries you have written return all of the rows from the table you are querying.  To do that you have only needed the 2 mandatory clauses of the SELECT statement, that is, the SELECT and the FROM clauses.

But there is another clause that even though it is optional, in real life you will almost always need to include it in your queries, and I’m talking about the WHERE clause.

This clause is very important because it allows you to filter the results of your queries so that you are not forced to always retrieve all of the rows from the table.

When you are working with tables with only a few rows, like the ones we are using to practice, it is usually not a big deal having to retrieve all of them, but when you have tables with thousands or millions of rows, then you definitely don’t want to retrieve all of them.

How does the WHERE clause work?

Okay, let’s see how the WHERE clause works.

It comprises 2 parts:  the WHERE keyword itself, and a condition that must evaluate to true for the rows that are going to be returned by the query.

So, if I write a query like this one:

SELECT *
FROM products
WHERE price < 10;

I get the list of all the products whose price is smaller than 10.

How does that happen?

Well, what happens is that every row in the table is reviewed to verify whether it meets the query’s condition or not.

So, in this case, the row for aspirin is evaluated first, and its price, which in this case is 5, is compared to 10, which is the value specified in the WHERE condition. So, since 5 is, in fact, smaller than 10, then the condition “5 < 10” evaluates to true and this row is included in the result set.

Then the database follows the same process with the row of penicillin, whose price is 10.  Now, 10 is equal to 10, right?  So, the condition  “10 < 10” evaluates to false, and thus, this row is not included in the result set.

And every row in the products table is evaluated this way, and when the process finishes, the final result set is returned.

Here’s Another example:

SELECT *
FROM products
WHERE expiration_date < '01-jun-2016';

How do I write Date Constants?

Expiration_date is a date column, remember?  But as you see, I can write dates as if they were strings, or varchars, as long as I use a correct format.

You will probably see a lot of people do this, but today I want to tell you:  “Don’t do it!”, and here’s why:

A date can only be compared to another date, so, when you do something like that, Oracle actually converts that string into a date, in the background, silently…  and that conversion is performed using the date format from your local settings.

So, look what I get if I change my date format and try to run the same query:

ORA-01858: a non-numeric character was found where a numeric was expected
“a non-numeric character was found where a numeric was expected”

*Cause:    The input data to be converted using a date format model wasincorrect.  The input data did not contain a number where a number was required by the format model.

*Action:   Fix the input data or the date format model to make sure the elements match in number and type.  Then retry the operation.

As you see, the exact same statement now produces an error.

So, since most of the times the queries you write are going to be run by other people with probably different local configurations, you have to write your queries in a way that they don’t depend on the local settings to work.

In the case of dates, you do that by using “date literals” instead of strings.

To write a date literal you use the reserved word “DATE” and then a string that represents your date, which must always be in this format:

‘YYYY-MM-DD’

That is, 4 digits for the year, 2 digits for the month and 2 digits for the day.

And this format is completely independent of local settings.  To define a date literal you will always use this same format, and it will run correctly on any system, regardless of its local settings.

So, I could write my previous query using a date literal this way:

SELECT *
FROM products
WHERE expiration_date < DATE '2016-06-01';

And you know what?  A lot of people don’t know about date literals, so, using them can be another way for you to stand out.

You could also explicitly convert strings to dates using a function, but we will talk about that a little later in the course.

Okay, WHERE conditions always include some kind of operator.  In this case, I used the “less than” or “Smaller than” comparison operator, but there are other types of operators, which I’ll cover in the next section.

Practice Challenge

Alright, it’s time to stop reading to have some fun working on your next task.

As always, please don’t continue until you have completed it.

For this lesson’s task you have to write 2 queries:

1) Display a list of departments whose monthly budget is greater than or equal to 20,000.  Please include only the department name and its budget.

2) Display a list of employees who were born before 1990, which includes the name, phone and birthdate.

Expected Output:

1)

2)

When you finish, move to the next lesson, where you will start writing more complex WHERE conditions.

See you there!