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

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