Curso de MariaDB Desde la Consola/Operadores de Comparación
Ir a la navegación
Ir a la búsqueda
- Operadores de comparación
- Operadores No igual
- Operador Menor que o igual
- Operador NULL-Safe igual
- Operadore Igualdad
- Operador Mayor que
- Operador Mayor que o igual
- Operador BETWEEN
- Operador COALESCE
- Operador GREATEST
- Operador IN
- Operador INTERVAL
- Operador IS
- Operador IS NOT
- Operador IS NOT NULL
- Operador IS NULL
- Operador ISNULL
- Operador LEAST
- Operador NOT BETWEEN
- Operador NOT IN
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)
