Just SQL Part III - Where is it?

Thursday Aug 4th 2005 by James Koopmann
Share:

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.

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

Using AND and OR conjunctives together

When constructing a SQL statement that contains both logical operators AND and OR, care must be given in regard to the order they are evaluated. The operator AND is considered to have a higher priority than the OR operator and thus gets evaluated first. It is best to explain this with a simple example. Suppose we wanted to display all dog breeds from the country 'USA' that where either big or small.

At first, we may devise the following SQL asking where the country is 'USA' AND breed size is 'Big' OR 'Small'. However, when we look at the output we soon notice that somehow a small breed from Australia was included. This is because the SQL was evaluated where country is USA and breed size is Big OR ANY breed size that is small.


SELECT country, breed, breed_size
  FROM dog_origin 
 WHERE country = 'USA' 
 AND breed_size = 'Big' 
 OR breed_size = 'Small'; 
COUNTRY   BREED                BREED_SIZE
--------- -------------------- ----------
USA       American Bulldog     Big
Australia Jack Russell Terrier Small

If we were to switch things around a bit, thinking we were asking for big or small breeds and where the country is 'USA' the output gets even worse. This is because this SQL is evaluated with the AND option first again, where the country is 'USA' and the breed is small OR ANY breed that is Big.

SELECT country, breed, breed_size
  FROM dog_origin 
 WHERE breed_size = 'Big' OR breed_size = 'Small' AND country = 'USA';
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

There are really two different ways we can solve this problem of wanting big or small breeds from the USA. The first solution will use the precedence of AND and OR. We must alter how we ask the question to stating we would like to display where the country is USA and the breed size is big or where the country is USA and the breed size is small. This way both AND operators are evaluated first and then the OR operator is second, thus giving us the proper result.


SELECT country, breed, breed_size
  FROM dog_origin
 WHERE country = 'USA' AND breed_size = 'Big'
    OR country = 'USA' AND breed_size = 'Small';
COUNTRY                        BREED                          BREED_SIZE
------------------------------ ------------------------------ ------------
USA                            American Bulldog               Big

This is a bit clunky and we can actually use, just as in mathematics, the parenthesis to impose order in evaluating the logical operators. Here is the best solution for our problem.


SELECT country, breed, breed_size
  FROM dog_origin 
 WHERE country = 'USA' AND ( breed_size = 'Big' OR breed_size = 'Small' );
COUNTRY                        BREED                          BREED_SIZE
------------------------------ ------------------------------ ------------
USA                            American Bulldog               Big

Additional predicates

In all of our examples in the article, we have only used the equality predicate. There are more, and here are the more common predicates used. I have introduced a new table called COUNTRY_POPULATION for these examples. Get familiar with this table, as it will be key in the next part of this series where we talk about joining tables on the predicates we have introduced here.

Table 2.
COUNTRY_POPULATION table

Country

Population

Year

Germany

82,424,609

2004

Germany

82,398,326

2003

Germany

83,251,851

2002

USA

293,027,571

2004

USA

290,342,554

2003

USA

280,562,489

2002

Switzerland

7,450,867

2004

Switzerland

7,318,638

2003

Switzerland

7,301,994

2002

Australia

19,913,144

2004

Australia

19,731,984

2003

Australia

19,546,792

2004



Predicate

Meaning

Example

Example Code

=

Equal

Display the population for all countries in the year 2003.

SELECT country, year, population

FROM country_population

WHERE year = 2003;

>

Greater than

Display all years where a country had over 10,000,000 in population.

SELECT country, year, population

FROM country_population

WHERE population > 10,000,000;

<

Less than

Display all years where a country had less than 10,000,000 in population.

SELECT country, year, population

FROM country_population

WHERE population < 10,000,000

BETWEEN

Between

Display all years where a country had between 1 and 10,000,000 in population.

SELECT country, year, population

FROM country_population

WHERE population BETWEEN 1 AND 10,000,000

!=

Not Equal

Display all countries population numbers where the country is not the USA.

SELECT country, year, population

FROM country_population

WHERE country != 'USA';

LIKE

Like

Returns rows where a column matches on similar character pattern.

Show the population for all countries that begin with the letters 'US'.

SELECT country, year, population

FROM country_population

WHERE country LIKE 'US%';

IN

Selection List

Returns rows where a column is equal to a value in a selection list.

Display the population numbers for years in 2002 or 2004

SELECT country, year, population

FROM country_population

WHERE year IN (2002, 2004);

This article has introduced the optional WHERE clause of the SQL SELECT statement. It is always good to talk proper nomenclature when discussing SQL and this article has introduced you to the terms conjunctives, search conditions, and predicates. Developing search conditions with the proper predicates and ordering conjunctives is the core of SELECTing information from your tables. Next time we will look at JOINing two tables together to answer even more complex questions of your data.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved