Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

If the division of Column1 by Column2 was okay—meaning that Column2 did not have a zero value—

the result of that division was returned. If it turned out wrong and the value was NULL, the 0 would be

returned. You can use numbers to clear things up:

SELECT IFNULL((12 / 2), 0) as Result

Your resultset would be

Result

6

If things went wrong

SELECT IFNULL((12 / 0), 0) as Result

the output would be

Result

0

If you did not use the IFNULL() function and tried to divide by zero, a NULL would be returned.

Instead, you wanted to catch the error before it caused any serious problems, so you used the

IFNULL() function.

The IFNULL() function is a great way to check for any unexpected results. This can be a lifesaver

when you have no control of the values that are being entered in a column.

AND, OR, NOT

MySQL also provides a set of logical operator functions. These functions will return a 1 if the values being
compared are true and a 0 if they are not.
The AND function or operator (depending how you look at it) will return a true value if both the values are
true and false if either of the values are false or NULL. Suppose you had the following statement:
SELECT 3 AND 4

The return value would be

1 or TRUE – both values are true (not 0 or NULL)

What would the following statement return?

SELECT 0 AND 3

If you guessed 0 , you are right. Both values have to be true for the expression to be true.

The AND operator can also be expressed as &&, as in many other programming languages.

The OR operator will return a true value if one of the values being compared is true. For example

SELECT 1 OR NULL

returns a value of 1 , and

SELECT NULL OR 0

returns a value of 0.

The OR operator can also be expressed as two pipe symbols. This is the symbol located right above the

Enter key on the keyboard (||). Again, most programming languages use this nomenclature.

The NOT operator negates the value of the argument. The exception is NULL. NOT NULL still returns

NULL. For example

SELECT NOT (1 + 1)

returns 0 and

SELECT NOT (1 – 1)

returns 1.

The NOT operator can also be expressed as the exclamation point (!).

These operator functions can be used to perform logical operations before a query gets to the WHERE

clause. This speeds up queries and increases overall performance.

String and Character Functions

The string and character functions can manipulate text before it is returned to the client. This can save some
time and processing power in your applications.