In most cases, we will not want every single row in the table and we need a way to identify which rows we want returned. We can add a filter to the query and limit the rows returned. While performance tuning is beyond the scope of this book, you would be surprised at the number of issues I have encountered because of bad filtering or no filtering at all. If you see a query that has no filter—be wary. We can filter on most types of information available in our table. Examples include date criteria, a quantity criteria, or a specific last name. The main objective is to make sure we are asking the right question. Once we know what we are looking for, we can apply our filter with the keyword WHERE. We then choose our filter column and apply the type of filter we want. Some types of filters include: Equal To ( = ), Less Than ( < ), and Greater Than ( > ). Other filters are in Table 1 below. Check the index if you are looking for something specific.
In this example, we will look for cats with the name of Garfield. We will use the Equal To operator on the column NameOfCat to find this information.

SELECT NameOfCat, BreedOfCat, ColorOfCat&amp;amp;nbsp; FROM Cat WHERE NameOfCat = 'Garfield';

OUTPUT

NameOfCat         BreedOfCat         ColorOfCat
—————–       ——————     —————
Garfield                Tabby                   Orange

 

You will notice there are quotes around the word Garfield in our statement. This is because the column NameOfCat is a text field and we want to indicate to the database server what kind of data we are working with. The single quotes help with this. If we wanted to find all of the dogs who have been with their owners for less than 2 months, we could use the Less Than operator ( < ) on the MonthsWithOwner column as shown in the query below.

SELECT NameOfDog, BreedOfDog, ColorOfDog&amp;amp;nbsp; FROM Dogs WHERE MonthsWithOwner &amp;amp;amp;lt; 2;

The results here have not been modified. We will only get back 9 records from this query.

OUTPUT

NameOfDog         BreedOfDog                 ColorOfDog
——————-      ——————–           ——————-
Odie                       Border-Aussie               Gray
Rover                     Cairnoodle                     Tan
Rover                     Cairoston                       White
Spike                      Cane Corso Italiano      Tan
Spike                      Cherokee Monarch      Yellow
Clifford                  Chow Pei                       Yellow
Dief                         Dachshund                  White and Yellow
Doc                         Dakotah Shepherd     Yellow
Rover                     La Pom                           Tan

(9 row(s) affected)

I

Want to follow along on your own database?

Do you want to follow along with the examples?  We have created a sample database you can use to follow along and try in your own database.  Go to the download page and follow the instructions.  Have fun on the SQL trail!