Curso de MariaDB Desde la Consola/Operadores de Comparación

De WikiCabal
Saltar a: navegación, buscar

Operadores de comparación

Operadores No igual

MariaDB [Prueba1]> SELECT '.01' <> '0.01';
+-----------------+
| '.01' <> '0.01' |
+-----------------+
|               1 |
+-------------- ---+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT .01 <> '0.01';
+---------------+
| .01 <> '0.01' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'zapp' <> 'zappp';
+-------------------+
| 'zapp' <> 'zappp' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT @x != @x;
+----------+
| @x != @x |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT @x != @y;
+----------+
| @x != @y |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Operador Menor que

MariaDB [Prueba1]> SELECT @x < @y;
+---------+
| @x < @y |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT @y < @x;
+---------+
| @y < @x |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 2 < 2;
+-------+
| 2 < 2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 3<'4';
+-------+
| 3<'4' |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'a'<'A';
+---------+
| 'a'<'A' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'A'<'a';
+---------+
| 'A'<'a' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

Operador Menor que o igual

MariaDB [Prueba1]> SELECT @x <= @y;
+----------+
| @x <= @y |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT @y <= @x;
+----------+
| @y <= @x |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 0.1 <= 2;
+----------+
| 0.1 <= 2 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'a'<='A';
+----------+
| 'a'<='A' |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Operador NULL-Safe igual

Este operador realiza una comparación de igualdad 
como el operador =, pero devuelve un valor de 1 
en lugar de NULL si ambos operandos son NULL, 
y 0 en vez de NULL si uno de los operandos es NULL. 
un a <=> b es equivalente a a = b O a ES NULL Y b es NULL).

MariaDB [Prueba1]> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
|     1 |        NULL |     NULL |
+-------+-------------+----------+
1 row in set (0.00 sec)

Operador Igualdad

Evalúa ambas expresiones SQL y devuelve 1 si son 
iguales, 0 si no son iguales, 
NULL si cualquiera expresión es NULL. 
Si las expresiones devolución diferentes tipos de 
datos (por ejemplo, un número y una cadena), se 
realiza una conversión de tipo.
También puede ser utilizado como un operador de 
asignación.

MariaDB [Prueba1]> SELECT 1 = 0;
+-------+
| 1 = 0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT '0' = 0;
+---------+
| '0' = 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT '0.0' = 0;
+-----------+
| '0.0' = 0 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT '0.01' = 0;
+------------+
| '0.01' = 0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT '.01' = 0.01;
+--------------+
| '.01' = 0.01 |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

MariaDB [Prueba1]> SELECT (5 * 2) = CONCAT('1', '0');
+----------------------------+
| (5 * 2) = CONCAT('1', '0') |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 1 = NULL;
+----------+
| 1 = NULL |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

Operador Mayor que

MariaDB [Prueba1]> SELECT 2 > 2;
+-------+
| 2 > 2 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'a' > 'b'; 
+-----------+
| 'a' > 'b' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

Operador Mayor que o igual

MariaDB [Prueba1]> SELECT 2 >= 2;
+--------+
| 2 >= 2 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'A' >= 'a';
+------------+
| 'A' >= 'a' |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT NULL >= NULL;
+--------------+
| NULL >= NULL |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 4 >= 2;
+--------+
| 4 >= 2 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT -4 >= 2;
+---------+
| -4 >= 2 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

Operador BETWEEN

expr BETWEEN min AND max

Si expr es mayor o igual que min y 
expr es menor o igual que max, ENTRE devuelve 1, 
de lo contrario, devuelve 0. 
Esto es equivalente a la expresión 
(min <= expr AND expr <= max) si todos los 
argumentos son del mismo tipo. De lo contrario, 
escriba conversión se realiza de acuerdo con 
las normas descritas en conversión de tipos, 
sino que se aplicaba a todos los tres argumentos.

MariaDB [Prueba1]> SELECT 1 BETWEEN 2 AND 3;
+-------------------+
| 1 BETWEEN 2 AND 3 |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'b' BETWEEN 'a' AND 'c';
+-------------------------+
| 'b' BETWEEN 'a' AND 'c' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 2 BETWEEN 2 AND '3';
+---------------------+
| 2 BETWEEN 2 AND '3' |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 2 BETWEEN 2 AND 'x-3';
+-----------------------+
| 2 BETWEEN 2 AND 'x-3' |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [Prueba1]> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x-3' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 1 BETWEEN 1 AND NULL;
+----------------------+
| 1 BETWEEN 1 AND NULL |
+----------------------+
|                 NULL |
+----------------------+
1 row in set (0.00 sec)

Operador COALESCE

COALESCE(value,...)

Devuelve el primer valor no NULL de la lista 
o NULL si no hay valores no NULL. Debe pasar 
al menos un parámetro.

MariaDB [Prueba1]> SELECT COALESCE(NULL,1);
+------------------+
| COALESCE(NULL,1) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT COALESCE(NULL,NULL,NULL);
+--------------------------+
| COALESCE(NULL,NULL,NULL) |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SET @a=NULL, @b=10;
Query OK, 0 rows affected (0.00 sec)

MariaDB [Prueba1]> SELECT COALESCE(@a, @b), IFNULL(@a, @b);
+------------------+----------------+
| COALESCE(@a, @b) | IFNULL(@a, @b) |
+------------------+----------------+
| 10               | 10             |
+------------------+----------------+
1 row in set (0.00 sec)

Operador GREATEST

GREATEST(val1,val2,...)

Con dos o más argumentos, retorna el argumento 
mayor (valor máximo). Los argumentos se comparan 
usando las mismas reglas que para LEAST().

MariaDB [Prueba1]> SELECT GREATEST(2,0);
+---------------+
| GREATEST(2,0) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT GREATEST(34.0,3.0,5.0,767.0);
+------------------------------+
| GREATEST(34.0,3.0,5.0,767.0) |
+------------------------------+
|                        767.0 |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT GREATEST('B','A','C') ;
+-----------------------+
| GREATEST('B','A','C') |
+-----------------------+
| C                     |
+-----------------------+
1 row in set (0.01 sec)

Operador IN

expr IN (value,...)

Devuelve 1 si expr es igual a cualquiera de 
los valores en la lista de IN, de lo contrario 
devuelve 0. Si todos los valores son constantes, 
son evaluados según el tipo de expr y ordenados. 
La búsqueda para el artículo a continuación se 
realiza usando una búsqueda binaria. Esto 
significa que es muy rápido si la lista de 
valores IN consiste enteramente de constantes. 
De lo contrario, conversión de tipos tiene lugar 
según las reglas descritas en el tipo de conversión, 
pero aplicado a todos los argumentos.
Si expr es NULL, en siempre devuelve NULL. 
Si al menos uno de los valores en la lista es NULL, 
y una de las comparaciones es true, el resultado es 1. 
Si al menos uno de los valores en la lista es nulo y 
ninguna de las comparaciones es true, el resultado es NULL.

MariaDB [Prueba1]> SELECT 2 IN (0,3,5,7);
+----------------+
| 2 IN (0,3,5,7) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 'wefwf' IN ('wee','wefwf','weg');
+----------------------------------+
| 'wefwf' IN ('wee','wefwf','weg') |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 1 IN ('1', '2', '3');
+----------------------+
| 1 IN ('1', '2', '3') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 1 IN (1, 2, NULL);
+-------------------+
| 1 IN (1, 2, NULL) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

MariaDB [Prueba1]> SELECT 5 IN (1, 2, NULL);
+-------------------+
| 5 IN (1, 2, NULL) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)

Operador INTERVAL

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

Devuelve el índice del último argumento que es menor que el primer 
argumento o es NULL. Devuelve 0 si N < N1, 1 si N < N2, 2 si N  < N3 
y así sucesivamente o -1 si N es NULL. Todos los argumentos se 
tratan como enteros. Es necesario que el N1 < N2 < N3 < ... < Nn 
para que esta función funcione correctamente. Esto es porque una 
rápida búsqueda binaria se utiliza.

MariaDB [(none)]> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
+--------------------------------------+
| INTERVAL(23, 1, 15, 17, 30, 44, 200) |
+--------------------------------------+
|                                    3 |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT INTERVAL(10, 1, 10, 100, 1000);
+--------------------------------+
| INTERVAL(10, 1, 10, 100, 1000) |
+--------------------------------+
|                              2 |
+--------------------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> SELECT INTERVAL(22, 23, 30, 44, 200);
+-------------------------------+
| INTERVAL(22, 23, 30, 44, 200) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT INTERVAL(10, 2, NULL);
+-----------------------+
| INTERVAL(10, 2, NULL) |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

Operador IS

IS valor boolean

Pruebas de un valor contra un valor booleano, donde valor booleano 
puede ser TRUE, FALSE o UNKNOWN.
Hay una diferencia importante entre usar IS TRUE o comparar un valor 
contra TRUE usando =. Cuando se usa =, sólo 1 es igual a TRUE. Pero 
cuando se usa IS TRUE, todos los valores que son lógicamente 
verdaderos (como un número > 1) devolución verdadero.

MariaDB [(none)]> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
+-----------+------------+-----------------+
| 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN |
+-----------+------------+-----------------+
|         1 |          1 |               1 |
+-----------+------------+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 2 = TRUE, 2 IS TRUE;
+----------+-----------+
| 2 = TRUE | 2 IS TRUE |
+----------+-----------+
|        0 |         1 |
+----------+-----------+
1 row in set (0.00 sec)

Operador IS NOT

IS NOT boolean_value

Pruebas de un valor contra un valor booleano, donde valor 
booleano puede ser TRUE, FALSE o UNKNOWN.

MariaDB [(none)]> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
+------------------+------------------+---------------------+
| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
+------------------+-------------------+
| NULL IS NOT TRUE | NULL IS NOT FALSE |
+------------------+-------------------+
|                1 |                 1 |
+------------------+-------------------+
1 row in set (0.00 sec)

Operador IS NOT NULL

Comprueba si un valor no es NULL.

MariaDB [(none)]> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
+---------------+---------------+------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
|             1 |             1 |                0 |
+---------------+---------------+------------------+
1 row in set (0.00 sec)

Operador IS NULL

Comprueba si un valor es NULL.

MariaDB [(none)]> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
|         0 |         0 |            1 |
+-----------+-----------+--------------+
1 row in set (0.00 sec)

Operador ISNULL

ISNULL(expr)

Comprueba si un expresión es NULL. Si es NULL devuelve 1
de lo contrario devuelve 0.
MariaDB [(none)]> SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.01 sec)

MariaDB [(none)]> SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Operador LEAST

LEAST(val1,val2,...)

Con dos o más argumentos, retorna el argumento menor 
(valor mínimo). Los argumentos se comparan usando las 
siguientes reglas:
    Si el valor devuelto se utiliza en un contexto INTEGER 
    o todos los argumentos son valores enteros, se comparan 
    como enteros.

    Si el valor devuelto se utiliza en un contexto REAL 
    o todos los argumentos son valores de real, se comparan 
    como reales.

    Si algún argumento es una cadena sensible a minúsculas 
    y mayúsculas, la comparación de cadenas entre honora 
    mayúsculas y minúsculas.

    En los demás casos, los argumentos se comparan como cadenas 
    de caracteres insensibles a mayúsculas y minúsculas.

    Least() retorna NULL si algún argumento es NULL.

MariaDB [(none)]> SELECT LEAST(2,0);
+------------+
| LEAST(2,0) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,767.0);
+---------------------------+
| LEAST(34.0,3.0,5.0,767.0) |
+---------------------------+
|                       3.0 |
+---------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT LEAST('B','A','C');
+--------------------+
| LEAST('B','A','C') |
+--------------------+
| A                  |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT LEAST('B', 'a', 'A','C');
+--------------------------+
| LEAST('B', 'a', 'A','C') |
+--------------------------+
| a                        |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,'',767.0);
+------------------------------+
| LEAST(34.0,3.0,5.0,'',767.0) |
+------------------------------+
| 0                            |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,NULL,767.0);
+--------------------------------+
| LEAST(34.0,3.0,5.0,NULL,767.0) |
+--------------------------------+
|                           NULL |
+--------------------------------+
1 row in set (0.00 sec)

Operador NOT BETWEEN

expr NOT BETWEEN min AND max

Esto es lo mismo que NOT (expr BETWEEN min y max).

MariaDB [(none)]> SELECT 1 NOT BETWEEN 2 AND 3;
+-----------------------+
| 1 NOT BETWEEN 2 AND 3 |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 'b' NOT BETWEEN 'a' AND 'c';
+-----------------------------+
| 'b' NOT BETWEEN 'a' AND 'c' |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 1 NOT BETWEEN 1 AND NULL;
+--------------------------+
| 1 NOT BETWEEN 1 AND NULL |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set (0.00 sec)

Operador NOT IN

expr NOT IN (value,...)

Lo mismo que NOT (expr IN (val1, val2, ...)).

MariaDB [(none)]> SELECT 2 NOT IN (0,3,5,7);
+--------------------+
| 2 NOT IN (0,3,5,7) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 'wefwf' NOT IN ('wee','wefwf','weg');
+--------------------------------------+
| 'wefwf' NOT IN ('wee','wefwf','weg') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 1 NOT IN ('1', '2', '3');
+--------------------------+
| 1 NOT IN ('1', '2', '3') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT NULL NOT IN (1, 2, 3);
+-----------------------+
| NULL NOT IN (1, 2, 3) |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 1 NOT IN (1, 2, NULL);
+-----------------------+
| 1 NOT IN (1, 2, NULL) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT 5 NOT IN (1, 2, NULL);
+-----------------------+
| 5 NOT IN (1, 2, NULL) |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set (0.00 sec)