|
Author : James F. Koopmann More at : www.jameskoopmann.com
|
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.
James F. Koopmann
In the previous Part II of this series we took a look 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 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. So 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
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’. But 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 gets evaluated, AND option first again, where country is ‘USA’ and 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 |
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 you to 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 are the core of SELECTing information from your tables. Next time we will look at we can join two tables together to answer even more complex questions of your data.