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;nbsp; FROM Cat WHERE NameOfCat = 'Garfield';
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;nbsp; FROM Dogs WHERE MonthsWithOwner &amp;amp;lt; 2;
The results here have not been modified. We will only get back 9 records from this query.
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)
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!