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

Country

Breed

Breed_size

Germany

German Shepherd Dog

Big

Germany

Dobermann

Big

Germany

Rottweiler

Big

USA

Siberian Husky

Medium

USA

Alaskan Malamute

Medium

USA

American Bulldog

Big

Switzerland

Bernese Mountain Dog

Big

Switzerland

Saint Bernard Dog

Big

Switzerland

Entlebuch Cattle Dog

Medium

Australia

Australian Cattle Dog

Medium

Australia

Jack Russell Terrier

Small

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles