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.
Previous
Back to DBAsupport.com
