Just SQL Part III – Where is it?

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