Offshore Software Outsourcing Company, offshore IT outsourcing services, offshore software development Russia

About ALTOROS: ALTOROS Systems is headquartered in Tampa, Florida and maintains an office near Boston, Massachusetts and technology development center in Belarus and Russia. ALTOROS specializes on providing value-added e-commerce and web-based software development and offshore software outsourcing services to emerging enterprises helping them successfully plan and implement business initiatives.

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These functions work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as needed (as in Perl).

MySQL performs comparisons using the following rules:

· If one or both arguments are NULL, the result of the comparison is NULL, except for the <=> operator.

· If both arguments in a comparison operation are strings, they are compared as strings.

· If both arguments are integers, they are compared as integers.

· Hexadecimal values are treated as binary strings if not compared to a number.

· If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly.

· In all other cases, the arguments are compared as floating-point (real) numbers.

By default, string comparisons are done in case-independent fashion using the current character set (ISO-8859-1 Latin1 by default, which also works excellently for English).

The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
         -> 0
mysql> SELECT 7 > '6x';
         -> 1
mysql> SELECT 0 > 'x6';
         -> 0
mysql> SELECT 0 = 'x6';
         -> 1

Equal:

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

Not equal:

mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1

Less than or equal:

mysql> SELECT 0.1 <= 2;
-> 1

Less than:

mysql> SELECT 2 < 2;
-> 0

Greater than or equal:

mysql> SELECT 2 >= 2;
-> 1

Greater than:

mysql> SELECT 2 > 2;
-> 0

<=>

NULL safe equal:

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0

IS NULL

IS NOT NULL

Test whether a value is or is not NULL:

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0 0 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0

To be able to work good with other programs, MySQL supports the following extra features when using IS NULL:

· You can find the last inserted row with:

SELECT * FROM tbl_name WHERE auto_col IS NULL

This can be disabled by setting SQL_AUTO_IS_NULL=0. See SET OPTION.

· For NOT NULL DATE and DATETIME columns you can find the special date 0000-00-00 by using:

SELECT * FROM tbl_name WHERE date_column IS NULL

This is needed to get some ODBC applications to work (as ODBC doesn't support a 0000-00-00 date)

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. The first argument (expr) determines how the comparison is performed as follows:

· If expr is a TIMESTAMP, DATE, or DATETIME column, MIN() and MAX() are formatted to the same format if they are constants.

· If expr is a case-insensitive string expression, a case-insensitive string comparison is done.

· If expr is a case-sensitive string expression, a case-sensitive string comparison is done.

· If expr is an integer expression, an integer comparison is done.

· Otherwise, a floating-point (real) comparison is done.

mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0

expr NOT BETWEEN min AND max

Same as NOT (expr BETWEEN min AND max).

expr IN (value,...)

Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion:

mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1

expr NOT IN (value,...)

Same as NOT (expr IN (value,...)).

ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0:

mysql> SELECT ISNULL(1+1);
-> 0
mysql> SELECT ISNULL(1/0);
-> 1

Note that a comparison of NULL values using = will always be false!

COALESCE(list)

Returns first non-NULL element in list:

mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL

INTERVAL(N,N1,N2,N3,...)

Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast):

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0

If you are comparing case-insensitive strings with any of the standard operators (=, <>..., but not LIKE) trailing whitespace (spaces, tabs and newlines) will be ignored.

mysql> SELECT "a" ="A \n";
-> 1