Home arrow MySQL arrow Page 4 - Optimizing Queries with Operators, Branching and Functions

Type Conversions with Logical and Arithmetic Operators - MySQL

This article will give you a good grounding in operators, branching and functions in MySQL, so you can make the database, instead of your own code, do the bulk of the work. It is the first of three parts, and excerpted from chapter four of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).

  1. Optimizing Queries with Operators, Branching and Functions
  2. Optimization 1: Separation of Logic and Formatting
  3. MySQL Operators
  4. Type Conversions with Logical and Arithmetic Operators
  5. Operators for Working with Sets
  6. Type Conversions in Comparisons
By: Apress Publishing
Rating: starstarstarstarstar / 13
March 30, 2006

print this article



Here are a few rules to keep in mind when working with logical and arithmetic operators in MySQL:

  • Any nonzero integer used with a logical operator evaluates as TRUE, even if it has a negative value.
  • When used with logical operators, fractional values are rounded to the nearest integer. This means that any N where 0.5  > ABS(N)  = 0 evaluates as FALSE. (In other words, any number whose absolute value is less than 0.5 is rounded to 0 when used with a logical operator.)
  • When adding, subtracting, multiplying, or dividing a mix of integers and decimals, the result will always be a decimal; 3 * 2.33 returns 6.99, and the expression 2.1 + 1.145 - 3.245 yields 0.000.
  • Any string value used in a logical or arithmetic context always evaluates to 0 (FALSE). For example, 'a' AND 1 returns 0 (FALSE) and 'b' + 4 returns 4.
  • Any arithmetic expression containing NULL returns NULL . This includes the expressions +NULL and -NULL .
  • Division by zero produces a NULL result.
  • Operators (like all MySQL keywords) are case-insensitive. So it makes no practical difference whether you write A AND B , A and B , or A && B. However, as stated elsewhere, we generally use uppercase for MySQL keywords throughout this book for the sake of easy recognition and consistency.

While this may all look quite familiar, be aware that what is true in your programming or scripting language of choice may not be so in MySQL. So, if you encounter what seems to be a bug in using arithmetic operators in a query, it may just be that MySQL behaves a bit differently than what you’re used to.

Comparison Operators

Like all programming languages, MySQL’s dialect of SQL has a more or less usual set of comparison operators, as well as a few unusual ones. The equals sign ( = ) serves as both the equality operator and the assignment operator, and an expression containing it evaluates to TRUE (1) if the operands are the same, and FALSE (0) if they’re not. If at least one of the operands is NULL , then the result is always NULL .

As mentioned in our discussion of datatypes in Chapter 2, a special “null-safe” comparison operator, <=> , returns 1 (TRUE) if the arguments are equal values or if both values are NULL , and 0 (FALSE) otherwise. In other words, 1 <=> 1 returns TRUE (1), 1 <=> NULL returns FALSE (0), and NULL <=> NULL returns TRUE (1). (Remember that the NULL value is not equal to any other value, not even itself.)

IS [NOT] NULL for testing whether or not a value is NULL , as discussed in Chapter 3. IS NULL can also be written as ISNULL() ; for example, SELECT ISNULL(1); returns 0. You are likely to find that ISNULL() is more portable to and from other databases than IS NULL or the <=> operator. In addition, MySQL supports the IFNULL() and NULLIF() flow-control operators that can be used in connection with null values, as discussed in the “Branching: Making Choices in Queries” section later in this chapter.

There are two ways of writing the inequality comparator. You may use either != or <>; they’re exactly the same so far as MySQL is concerned. The <> notation seems to be more common, and it’s what we prefer to use ourselves, but the choice is entirely up to you. (As always, we recommend you choose one or the other, and stick with it.) Note that NULL compared with any value—even itself—using <> or != yields the null value. However, when performing branching operations, you’re generally checking to see whether a condition is true or not, and since NULL isn’t true, a null result still causes you to follow the “false” branch.

We’ll discuss IF() and the other branching operators later in this chapter.

Both forms of the inequality operator (<> and !=) are standard SQL and supported by most other relational databases.

MySQL also has greater-than, less-than, greater-than-or-equal, and less-than-or-equal operators, which are written (in order) >, <, >=, and <=. These behave more or less just as you would expect. As with the equality and inequality operators, any comparison involving NULL and using one of these operators will always yield a NULL result.

Some relational databases provide additional comparison operators meaning “not greater than” (!> or />) and “not less than” (!< or /< ). These are not part of the SQL standard and are not currently supported in MySQL.

There’s also a shortcut for determining whether a given value lies within a certain range. The BETWEEN operator is used as shown here.

BETWEEN can also be used with nonnumeric datatypes such as strings. Note that (as usual) string comparisons are case-insensitive unless you employ the BINARY modifier.

Dates as well as strings can be compared.

If the value immediately before the AND is not less than the value following it, then 0 will be always be returned.

There’s an additional operator for use in comparing strings.The LIKE operator allows you to do “fuzzy” matching with wildcards to find strings that are similar to one another. We’ll discuss LIKE in more detail in the “String Functions and Regular Expressions” section later in this chapter.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: