Just SQL Part III - Where is it? - Page 2

August 4, 2005

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers