How Operator Precedence Affects MySQL SELECT Queries

September 22, 2010

When joining tables on common fields, if you're not diligent in the use of AND-OR combinations in the criteria clause, be prepared for unexpected results! Learn how to break filtering criteria into individual steps and how to group AND-OR statements to most effectively isolate the data that you wish to retrieve.

The following code can be used to create and
populate the employees and shops tables. If youve downloaded
the table data from my recent articles, you dont need these as they are
the same:

This result set is probably not what the query writer had
in mind (notice the three rows of Kristen Ruegg):

name

gender

Monthly Salary

Jon
Simpson

m

$4,500.00

Barbara
Breitenmoser

m

(NULL)

Kirsten
Ruegg

f

$5,600.00

Kirsten
Ruegg

f

$5,600.00

Kirsten
Ruegg

f

$5,600.00

So, what went wrong?

It all comes down to something called
Operator Precedence, which is a concept that applies to any discipline that
utilizes operators; the two main ones being mathematics and computer languages. Precedence
rules, also known as the order of operations, are used to clarify which procedures
should be performed first. In math for
instance, multiplication and division take precedence over addition and subtraction. Hence,
the expression 10 + 100 / 5 equals 30 and not 22. The same idea applies
to SQL, where precedence rules govern which operator will be applied in a
given expression before the others. Here is the operator list for MySQL,
from first to last:

Please before Thank You, AND Before OR

While both are quite low on the precedence scale, the AND
does come just before the Exclusive OR (XOR) and OR operators. So what does
that mean for our query above? To answer that question, lets take a look
at the statement again, but with the implied parentheses:

In last weeks Identifying
and Eliminating the Dreaded Cartesian Product article, we saw how improper
or missing table joins can wreak devastating effects on our results. And
thats exactly what is happening here. The AND clause is sticking to the
line above it, causing the join to be limited to it, while leaving the OR
part of the statement left standing on its own. After including the implied
parentheses around the first two criteria statements, we can see that the
query is fetching employees that work in the Zurich office, but it is also
retrieving an employee for every row in the shops table whose salary is over
$5500. Careful analysis of the data confirms that Kirsten Ruegg is in
fact the only employee who makes that amount. The fact that she works in
New York is irrelevant because the OR statement is not linked to the shops
table. That, my friends, is a Cartesian Product.

Linking the OR statement to the
shops table removes the Cartesian Product and returns the correct results:

The Proper Use of Parentheses

The moral of this story is clear: Always Enclose Criteria
Selections in Parentheses. In fact, the more filtering criteria that
you have, the more important that it becomes to use parentheses to isolate
specific criteria, especially when mixing operators of differing precedence. The
following SELECT query also includes the IS and NOT operators.

While the above statement does succeed in eliminating
the row that contained the NULL salary, it still produces the same Cartesian
Product as the original query:

Unfortunately, knowing that we need parentheses
is only half the battle. The other half is to know where to put them! Guess
wrong, and youll retrieve the wrong data. This is where it helps to be
really clear on what exactly you want to see. Watch what happens when we
include different lines within the parentheses. First, the same AND-OR combination
as the previous example:

We can follow the order of operations
to see what should come back. To begin, well execute the first statement
above to see the employees who work in the Zurich shop. The easiest way
to accomplish this is to comment out the lines that we dont want. There
are two kinds of comments to choose from:

A double dash comments out one line: --This
is a comment.

/* and */ enclose one or more lines
of comments. Heres the same query as above with the last two lines commented
out:

Due to the higher operator precedent
of the AND keyword, this SELECT statement says:

Show me the employee info for those
who work in the Zurich shop

AND whose salary is any non-null value.

In addition to those, show me employees
whose gender is female.

This causes Kirsten Ruegg to be picked up, as
she satisfies the female gender criteria, even though she does not work in
the Zurich shop:

name

gender

Salary

Jon
Simpson

m

$4,500.00

Barbara
Breitenmoser

f

(NULL)

Kirsten
Ruegg

f

$5,600.00

As you can see, operator precedence can have
subtle but important effects on your SELECT query result sets. It pays to
go through the trouble of breaking complex criteria into individual steps
and to include parentheses wherever you want to group multiple criteria together.