As we saw in chapter 5, the WHERE clause offers many useful options for filtering results based on specific conditions. We looked at several examples of filtering on a single value using the AND and OR operators. Now we’ll expand that concept to filter on even more values, including a list of specific values or ranges of unspecified values.
These are examples of positive searches, in which we try to match values that we want to see in the results of our queries. Because often, we’ll want to do the opposite and see all the values except some specific filter conditions, we’ll also see how to negate any of the conditions we’ve covered. Let’s start by looking at a new operator for the WHERE clause.
6.1 Filtering on specific values
Previously, we looked at a basic search, finding the TitleNames for titles that had a certain Price. If we want to query titles that had a Price of $10.95, for example, we’d write our SQL like this:
SELECT
TitleName,
Price
FROM title
WHERE Price = 10.95;
But what if we want to find the titles with a Price of either $10.95 or $12.95? Now we know that we can write SQL to do this using the OR operator, so we could write a SQL query like this (output shown in figure 6.1):
SELECT
TitleName,
Price
FROM title
WHERE Price = 10.95
OR Price = 12.95;
Figure 6.1 The results of a query with filter conditions for a Price of 10.95 or 12.95
