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