The MOD() function takes two arguments. The first argument is the numerator, and the second
argument is the divisor. To express the previous example, the statement would look like the following:
SELECT MOD(14, 3) as ResultThe output would be
Result
2
As you can see, this function differs from the previously discussed functions. If you try to divide by 0,
(MOD(4, 0)), MySQL will return a null value. To separate arguments inside a function, use a comma.
This is much like any other programming language. In fact, MySQL uses C to perform these functions. If
you wanted to create your own function, you could. Day 18, "How MySQL Compares," covers user-
defined functions.
This covers the basic numeric functions. As you can see, they are relatively simple to use. Refer to
Appendix B to see the additional numeric functions that MySQL has to offer.Logical and Conditional Functions
The next few sections will cover logical and conditional functions as they are used by MySQL.
IF(), IFNULL(), and CASE
MySQL provides a way to perform conditional testing on expressions. It is a little crude, but it works. It is
basically a function that takes three arguments. If the expression in the first argument is true, it will return the
second argument. If expression one is false, it will return the third argument. Say you are comparing Column
1 to Column 2. If Column 1 is larger than Column 2, you want MySQL to return a 1 to your program. If
Column 2 is larger, you want a 0 returned to your program. The statement to use would look like the
following:
SELECT IF(Column1 > Column2, 1, 0) FROM Orders
So, if Column1 had a value of 12 and Column2 had a value of 3 , this statement would return 1 (the
second argument). If Column1 had a value of 3 , and Column2 had a value of 12 , this statement would
return 0 (the third argument). As you can see, this could take a little getting used to.
This function is quite useful when you need a straight comparison of columns. You may be wondering
why you would want to use this instead of placing your conditions in the WHERE clause. Doing it this way
is faster than using a WHERE clause. If you use a WHERE clause, MySQL will have to parse the entire
statement, generate a query plan, choose the right keys, and then perform the statement. When a
function is used, a query plan is not generated. The results are returned much faster. If you are going to
do some straight comparisons, use this function instead of a WHERE clause.
If you need to compare more than one value, MySQL provides a CASE statement, which is pretty similar
to the CASE statement in C. The syntax for the CASE statement is as follows:
SELECT CASE value WHEN comparison THEN result ELSE results
So, to use the previous example, you could have used a CASE statement as follows:
SELECT CASE 1 WHEN Column1 > Column2 THEN 0 ELSE 1
The CASE statement allows you to make multiple comparisons. Suppose you wanted to compare the
value of three columns. You are looking to see if a column has a value of "Y". To do this, you could use
the following statement:
SELECT CASE 1 WHEN Column1 = "Y" THEN 1 WHEN Column2 = "Y" THEN 2
WHEN Column3 = "Y" THEN 3 ELSE "NONE"
This would return the first number of the column that was equal to "Y". If none of the columns had a
"Y" in them, this statement would return the word "NONE". This is a handy function when you need to
perform multiple comparisons. As before, it is much faster to perform the comparison this way, because
a query plan will not be generated.
The other comparison operation MySQL has is the IFNULL() function. This function will return the first
argument if it is not NULL; it will return the second argument if it is NULL. For example, you want to
make sure your division function worked and that you did not accidentally divide by zero. To do this, you
could use the following statement:
SELECT IFNULL((Column1 / Column2), 0))