Just SQL Part III - Where is it?

August 4, 2005

We do not always want to SELECT everything from a table. The matter of finding the information required is a function of implementing the optional WHERE clause of the SELECT statement.

In the previous Part of this series we looked at the 'simple SELECT' statement. This involved the four required pieces of a SQL statement. Namely the key word SELECT, a select list or columns we wanted to return from the table, the FROM key word to specify the table information was going to be retrieved from, and then the name of the table itself. The simple SELECT statement when running will return every row in a table. More often then not, we are only concerned with a subset of rows from a table. That is where this article picks up and explores the use of the optional WHERE clause.

Table 1.
DOG_ORIGIN table

The WHERE clause

The optional WHERE clause, simply stated, imposes search criteria on top of a SELECT statement giving the affect of conditionally selecting those rows from a table you are interested in displaying. Again using Table 1 from Part II of this series there was a simple select statement that listed the all countries of origin for dog breeds. The SQL looked like this.

SELECT country, breed FROM dog_origin;

COUNTRY                        BREED
------------------------------ -----------------------
Germany                        German Shepherd Dog
Germany                        Dobermann
Germany                        Rottweiler
USA                            Siberian Husky
USA                            Alaskan Malamute
USA                            American Bulldog
Switzerland                    Bernese Mountain Dog
Switzerland                    Saint Bernard Dog
Switzerland                    Entlebuch Cattle Dog
Australia                      Australian Cattle Dog
Australia                      Jack Russell Terrier

Now suppose we were interested in dog breeds only from Germany we would need to put a WHERE condition on the SELECT statement. The following WHERE clause produces a result set for all dog breeds from Germany. The "country = 'Germany'" part of the WHERE clause is considered the search condition that must be met for a row to be returned. The equal sign is called the predicate of the search conditional. Also, please note that the country 'Germany' must match exactly to the data stored in the DOG_ORIGIN table. A condition of 'WHERE country = 'GERMANY' would not work.

SELECT breed FROM dog_origin WHERE country = 'Germany';

BREED
-----------------------
German Shepherd Dog
Dobermann
Rottweiler

The AND conjunctive

Often times a single search condition is not enough and we would like to search on multiple conditions. The logical operator AND facilitates this purpose and is considered part of a WHERE clause. Just as you would suspect, when using the AND conjunctive, both conditions between the AND must be true for a result set to be returned. Therefore, if we wanted to look for medium sized dogs in the USA we would construct a query like the following:

SELECT breed 
  FROM dog_origin WHERE country = 'USA' AND breed_size = 'Medium';

BREED
-------------------
Siberian Husky
Alaskan Malamute

The OR conjunctive

Where the AND conjunctive is mandatory for both conditions and returning row, the logical operator OR specifies that one or the other conditions need only be true for a result set to be returned. This is just as you would suspect and if we wanted to display the dogs from countries that were big or small we could construct the following:

SELECT country, breed, breed_size 
  FROM dog_origin WHERE breed_size = 'Big' OR breed_size = 'Small';

COUNTRY                        BREED                          BREED_SIZE
------------------------------ ------------------------------ ------------
Germany                        German Shepherd Dog            Big
Germany                        Dobermann                      Big
Germany                        Rottweiler                     Big
USA                            American Bulldog               Big
Switzerland                    Bernese Mountain Dog           Big
Switzerland                    Saint Bernard Dog              Big
Australia                      Jack Russell Terrier           Small