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

De WikiCabal
Ir a la navegación Ir a la búsqueda

Operadores de comparación

Operadores No igual

 1 MariaDB [Prueba1]> SELECT '.01' <> '0.01';
 2 +-----------------+
 3 | '.01' <> '0.01' |
 4 +-----------------+
 5 |               1 |
 6 +-------------- ---+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [Prueba1]> SELECT .01 <> '0.01';
10 +---------------+
11 | .01 <> '0.01' |
12 +---------------+
13 |             0 |
14 +---------------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT 'zapp' <> 'zappp';
18 +-------------------+
19 | 'zapp' <> 'zappp' |
20 +-------------------+
21 |                 1 |
22 +-------------------+
23 1 row in set (0.00 sec)
24 
25 MariaDB [Prueba1]> SELECT @x != @x;
26 +----------+
27 | @x != @x |
28 +----------+
29 |        0 |
30 +----------+
31 1 row in set (0.00 sec)
32 
33 MariaDB [Prueba1]> SELECT @x != @y;
34 +----------+
35 | @x != @y |
36 +----------+
37 |        1 |
38 +----------+
39 1 row in set (0.00 sec)

Operador Menor que

 1 MariaDB [Prueba1]> SELECT @x < @y;
 2 +---------+
 3 | @x < @y |
 4 +---------+
 5 |       1 |
 6 +---------+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [Prueba1]> SELECT @y < @x;
10 +---------+
11 | @y < @x |
12 +---------+
13 |       0 |
14 +---------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT 2 < 2;
18 +-------+
19 | 2 < 2 |
20 +-------+
21 |     0 |
22 +-------+
23 1 row in set (0.00 sec)
24 
25 MariaDB [Prueba1]> SELECT 3<'4';
26 +-------+
27 | 3<'4' |
28 +-------+
29 |     1 |
30 +-------+
31 1 row in set (0.00 sec)
32 
33 MariaDB [Prueba1]> SELECT 'a'<'A';
34 +---------+
35 | 'a'<'A' |
36 +---------+
37 |       0 |
38 +---------+
39 1 row in set (0.00 sec)
40 
41 MariaDB [Prueba1]> SELECT 'A'<'a';
42 +---------+
43 | 'A'<'a' |
44 +---------+
45 |       0 |
46 +---------+
47 1 row in set (0.00 sec)

Operador Menor que o igual

 1 MariaDB [Prueba1]> SELECT @x <= @y;
 2 +----------+
 3 | @x <= @y |
 4 +----------+
 5 |        1 |
 6 +----------+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [Prueba1]> SELECT @y <= @x;
10 +----------+
11 | @y <= @x |
12 +----------+
13 |        0 |
14 +----------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT 0.1 <= 2;
18 +----------+
19 | 0.1 <= 2 |
20 +----------+
21 |        1 |
22 +----------+
23 1 row in set (0.00 sec)
24 
25 MariaDB [Prueba1]> SELECT 'a'<='A';
26 +----------+
27 | 'a'<='A' |
28 +----------+
29 |        1 |
30 +----------+
31 1 row in set (0.00 sec)

Operador NULL-Safe igual

 1 Este operador realiza una comparación de igualdad 
 2 como el operador =, pero devuelve un valor de 1 
 3 en lugar de NULL si ambos operandos son NULL, 
 4 y 0 en vez de NULL si uno de los operandos es NULL. 
 5 un a <=> b es equivalente a a = b O a ES NULL Y b es NULL).
 6 
 7 MariaDB [Prueba1]> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
 8 +---------+---------------+------------+
 9 | 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
10 +---------+---------------+------------+
11 |       1 |             1 |          0 |
12 +---------+---------------+------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT 1 = 1, NULL = NULL, 1 = NULL;
16 +-------+-------------+----------+
17 | 1 = 1 | NULL = NULL | 1 = NULL |
18 +-------+-------------+----------+
19 |     1 |        NULL |     NULL |
20 +-------+-------------+----------+
21 1 row in set (0.00 sec)

Operador Igualdad

 1 Evalúa ambas expresiones SQL y devuelve 1 si son 
 2 iguales, 0 si no son iguales, 
 3 NULL si cualquiera expresión es NULL. 
 4 Si las expresiones devolución diferentes tipos de 
 5 datos (por ejemplo, un número y una cadena), se 
 6 realiza una conversión de tipo.
 7 También puede ser utilizado como un operador de 
 8 asignación.
 9 
10 MariaDB [Prueba1]> SELECT 1 = 0;
11 +-------+
12 | 1 = 0 |
13 +-------+
14 |     0 |
15 +-------+
16 1 row in set (0.00 sec)
17 
18 MariaDB [Prueba1]> SELECT '0' = 0;
19 +---------+
20 | '0' = 0 |
21 +---------+
22 |       1 |
23 +---------+
24 1 row in set (0.00 sec)
25 
26 MariaDB [Prueba1]> SELECT '0.0' = 0;
27 +-----------+
28 | '0.0' = 0 |
29 +-----------+
30 |         1 |
31 +-----------+
32 1 row in set (0.00 sec)
33 
34 MariaDB [Prueba1]> SELECT '0.01' = 0;
35 +------------+
36 | '0.01' = 0 |
37 +------------+
38 |          0 |
39 +------------+
40 1 row in set (0.00 sec)
41 
42 MariaDB [Prueba1]> SELECT '.01' = 0.01;
43 +--------------+
44 | '.01' = 0.01 |
45 +--------------+
46 |            1 |
47 +--------------+
48 1 row in set (0.01 sec)
49 
50 MariaDB [Prueba1]> SELECT (5 * 2) = CONCAT('1', '0');
51 +----------------------------+
52 | (5 * 2) = CONCAT('1', '0') |
53 +----------------------------+
54 |                          1 |
55 +----------------------------+
56 1 row in set (0.00 sec)
57 
58 MariaDB [Prueba1]> SELECT 1 = NULL;
59 +----------+
60 | 1 = NULL |
61 +----------+
62 |     NULL |
63 +----------+
64 1 row in set (0.00 sec)
65 
66 MariaDB [Prueba1]> SELECT NULL = NULL;
67 +-------------+
68 | NULL = NULL |
69 +-------------+
70 |        NULL |
71 +-------------+
72 1 row in set (0.00 sec)
73 
74 MariaDB [Prueba1]> SELECT NULL <=> NULL;
75 +---------------+
76 | NULL <=> NULL |
77 +---------------+
78 |             1 |
79 +---------------+
80 1 row in set (0.00 sec)

Operador Mayor que

 1 MariaDB [Prueba1]> SELECT 2 > 2;
 2 +-------+
 3 | 2 > 2 |
 4 +-------+
 5 |     0 |
 6 +-------+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [Prueba1]> SELECT 'b' > 'a';
10 +-----------+
11 | 'b' > 'a' |
12 +-----------+
13 |         1 |
14 +-----------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT 'a' > 'b'; 
18 +-----------+
19 | 'a' > 'b' |
20 +-----------+
21 |         0 |
22 +-----------+
23 1 row in set (0.00 sec)

Operador Mayor que o igual

 1 MariaDB [Prueba1]> SELECT 2 >= 2;
 2 +--------+
 3 | 2 >= 2 |
 4 +--------+
 5 |      1 |
 6 +--------+
 7 1 row in set (0.00 sec)
 8 
 9 MariaDB [Prueba1]> SELECT 'A' >= 'a';
10 +------------+
11 | 'A' >= 'a' |
12 +------------+
13 |          1 |
14 +------------+
15 1 row in set (0.00 sec)
16 
17 MariaDB [Prueba1]> SELECT NULL >= NULL;
18 +--------------+
19 | NULL >= NULL |
20 +--------------+
21 |         NULL |
22 +--------------+
23 1 row in set (0.00 sec)
24 
25 MariaDB [Prueba1]> SELECT 4 >= 2;
26 +--------+
27 | 4 >= 2 |
28 +--------+
29 |      1 |
30 +--------+
31 1 row in set (0.00 sec)
32 
33 MariaDB [Prueba1]> SELECT -4 >= 2;
34 +---------+
35 | -4 >= 2 |
36 +---------+
37 |       0 |
38 +---------+
39 1 row in set (0.00 sec)

Operador BETWEEN

 1 expr BETWEEN min AND max
 2 
 3 Si expr es mayor o igual que min y 
 4 expr es menor o igual que max, ENTRE devuelve 1, 
 5 de lo contrario, devuelve 0. 
 6 Esto es equivalente a la expresión 
 7 (min <= expr AND expr <= max) si todos los 
 8 argumentos son del mismo tipo. De lo contrario, 
 9 escriba conversión se realiza de acuerdo con 
10 las normas descritas en conversión de tipos, 
11 sino que se aplicaba a todos los tres argumentos.
12 
13 MariaDB [Prueba1]> SELECT 1 BETWEEN 2 AND 3;
14 +-------------------+
15 | 1 BETWEEN 2 AND 3 |
16 +-------------------+
17 |                 0 |
18 +-------------------+
19 1 row in set (0.00 sec)
20 
21 MariaDB [Prueba1]> SELECT 'b' BETWEEN 'a' AND 'c';
22 +-------------------------+
23 | 'b' BETWEEN 'a' AND 'c' |
24 +-------------------------+
25 |                       1 |
26 +-------------------------+
27 1 row in set (0.00 sec)
28 
29 MariaDB [Prueba1]> SELECT 2 BETWEEN 2 AND '3';
30 +---------------------+
31 | 2 BETWEEN 2 AND '3' |
32 +---------------------+
33 |                   1 |
34 +---------------------+
35 1 row in set (0.00 sec)
36 
37 MariaDB [Prueba1]> SELECT 2 BETWEEN 2 AND 'x-3';
38 +-----------------------+
39 | 2 BETWEEN 2 AND 'x-3' |
40 +-----------------------+
41 |                     0 |
42 +-----------------------+
43 1 row in set, 1 warning (0.00 sec)
44 
45 MariaDB [Prueba1]> show warnings;
46 +---------+------+-----------------------------------------+
47 | Level   | Code | Message                                 |
48 +---------+------+-----------------------------------------+
49 | Warning | 1292 | Truncated incorrect DOUBLE value: 'x-3' |
50 +---------+------+-----------------------------------------+
51 1 row in set (0.00 sec)
52 
53 MariaDB [Prueba1]> SELECT 1 BETWEEN 1 AND NULL;
54 +----------------------+
55 | 1 BETWEEN 1 AND NULL |
56 +----------------------+
57 |                 NULL |
58 +----------------------+
59 1 row in set (0.00 sec)

Operador COALESCE

 1 COALESCE(value,...)
 2 
 3 Devuelve el primer valor no NULL de la lista 
 4 o NULL si no hay valores no NULL. Debe pasar 
 5 al menos un parámetro.
 6 
 7 MariaDB [Prueba1]> SELECT COALESCE(NULL,1);
 8 +------------------+
 9 | COALESCE(NULL,1) |
10 +------------------+
11 |                1 |
12 +------------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT COALESCE(NULL,NULL,NULL);
16 +--------------------------+
17 | COALESCE(NULL,NULL,NULL) |
18 +--------------------------+
19 |                     NULL |
20 +--------------------------+
21 1 row in set (0.00 sec)
22 
23 MariaDB [Prueba1]> SET @a=NULL, @b=10;
24 Query OK, 0 rows affected (0.00 sec)
25 
26 MariaDB [Prueba1]> SELECT COALESCE(@a, @b), IFNULL(@a, @b);
27 +------------------+----------------+
28 | COALESCE(@a, @b) | IFNULL(@a, @b) |
29 +------------------+----------------+
30 | 10               | 10             |
31 +------------------+----------------+
32 1 row in set (0.00 sec)

Operador GREATEST

 1 GREATEST(val1,val2,...)
 2 
 3 Con dos o más argumentos, retorna el argumento 
 4 mayor (valor máximo). Los argumentos se comparan 
 5 usando las mismas reglas que para LEAST().
 6 
 7 MariaDB [Prueba1]> SELECT GREATEST(2,0);
 8 +---------------+
 9 | GREATEST(2,0) |
10 +---------------+
11 |             2 |
12 +---------------+
13 1 row in set (0.00 sec)
14 
15 MariaDB [Prueba1]> SELECT GREATEST(34.0,3.0,5.0,767.0);
16 +------------------------------+
17 | GREATEST(34.0,3.0,5.0,767.0) |
18 +------------------------------+
19 |                        767.0 |
20 +------------------------------+
21 1 row in set (0.00 sec)
22 
23 MariaDB [Prueba1]> SELECT GREATEST('B','A','C') ;
24 +-----------------------+
25 | GREATEST('B','A','C') |
26 +-----------------------+
27 | C                     |
28 +-----------------------+
29 1 row in set (0.01 sec)

Operador IN

 1 expr IN (value,...)
 2 
 3 Devuelve 1 si expr es igual a cualquiera de 
 4 los valores en la lista de IN, de lo contrario 
 5 devuelve 0. Si todos los valores son constantes, 
 6 son evaluados según el tipo de expr y ordenados. 
 7 La búsqueda para el artículo a continuación se 
 8 realiza usando una búsqueda binaria. Esto 
 9 significa que es muy rápido si la lista de 
10 valores IN consiste enteramente de constantes. 
11 De lo contrario, conversión de tipos tiene lugar 
12 según las reglas descritas en el tipo de conversión, 
13 pero aplicado a todos los argumentos.
14 Si expr es NULL, en siempre devuelve NULL. 
15 Si al menos uno de los valores en la lista es NULL, 
16 y una de las comparaciones es true, el resultado es 1. 
17 Si al menos uno de los valores en la lista es nulo y 
18 ninguna de las comparaciones es true, el resultado es NULL.
19 
20 MariaDB [Prueba1]> SELECT 2 IN (0,3,5,7);
21 +----------------+
22 | 2 IN (0,3,5,7) |
23 +----------------+
24 |              0 |
25 +----------------+
26 1 row in set (0.00 sec)
27 
28 MariaDB [Prueba1]> SELECT 'wefwf' IN ('wee','wefwf','weg');
29 +----------------------------------+
30 | 'wefwf' IN ('wee','wefwf','weg') |
31 +----------------------------------+
32 |                                1 |
33 +----------------------------------+
34 1 row in set (0.00 sec)
35 
36 MariaDB [Prueba1]> SELECT 1 IN ('1', '2', '3');
37 +----------------------+
38 | 1 IN ('1', '2', '3') |
39 +----------------------+
40 |                    1 |
41 +----------------------+
42 1 row in set (0.00 sec)
43 
44 MariaDB [Prueba1]> SELECT NULL IN (1, 2, 3);
45 +-------------------+
46 | NULL IN (1, 2, 3) |
47 +-------------------+
48 |              NULL |
49 +-------------------+
50 1 row in set (0.00 sec)
51 
52 MariaDB [Prueba1]> SELECT 1 IN (1, 2, NULL);
53 +-------------------+
54 | 1 IN (1, 2, NULL) |
55 +-------------------+
56 |                 1 |
57 +-------------------+
58 1 row in set (0.00 sec)
59 
60 MariaDB [Prueba1]> SELECT 5 IN (1, 2, NULL);
61 +-------------------+
62 | 5 IN (1, 2, NULL) |
63 +-------------------+
64 |              NULL |
65 +-------------------+
66 1 row in set (0.00 sec)

Operador INTERVAL

 1 INTERVAL(N,N1,N2,N3,...)
 2 
 3 Devuelve el índice del último argumento que es menor que el primer 
 4 argumento o es NULL. Devuelve 0 si N < N1, 1 si N < N2, 2 si N  < N3 
 5 y así sucesivamente o -1 si N es NULL. Todos los argumentos se 
 6 tratan como enteros. Es necesario que el N1 < N2 < N3 < ... < Nn 
 7 para que esta función funcione correctamente. Esto es porque una 
 8 rápida búsqueda binaria se utiliza.
 9 
10 MariaDB [(none)]> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
11 +--------------------------------------+
12 | INTERVAL(23, 1, 15, 17, 30, 44, 200) |
13 +--------------------------------------+
14 |                                    3 |
15 +--------------------------------------+
16 1 row in set (0.00 sec)
17 
18 MariaDB [(none)]> SELECT INTERVAL(10, 1, 10, 100, 1000);
19 +--------------------------------+
20 | INTERVAL(10, 1, 10, 100, 1000) |
21 +--------------------------------+
22 |                              2 |
23 +--------------------------------+
24 1 row in set (0.01 sec)
25 
26 MariaDB [(none)]> SELECT INTERVAL(22, 23, 30, 44, 200);
27 +-------------------------------+
28 | INTERVAL(22, 23, 30, 44, 200) |
29 +-------------------------------+
30 |                             0 |
31 +-------------------------------+
32 1 row in set (0.00 sec)
33 
34 MariaDB [(none)]> SELECT INTERVAL(10, 2, NULL);
35 +-----------------------+
36 | INTERVAL(10, 2, NULL) |
37 +-----------------------+
38 |                     2 |
39 +-----------------------+
40 1 row in set (0.00 sec)

Operador IS

 1 IS valor boolean
 2 
 3 Pruebas de un valor contra un valor booleano, donde valor booleano 
 4 puede ser TRUE, FALSE o UNKNOWN.
 5 Hay una diferencia importante entre usar IS TRUE o comparar un valor 
 6 contra TRUE usando =. Cuando se usa =, sólo 1 es igual a TRUE. Pero 
 7 cuando se usa IS TRUE, todos los valores que son lógicamente 
 8 verdaderos (como un número > 1) devolución verdadero.
 9 
10 MariaDB [(none)]> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
11 +-----------+------------+-----------------+
12 | 1 IS TRUE | 0 IS FALSE | NULL IS UNKNOWN |
13 +-----------+------------+-----------------+
14 |         1 |          1 |               1 |
15 +-----------+------------+-----------------+
16 1 row in set (0.00 sec)
17 
18 MariaDB [(none)]> SELECT 2 = TRUE, 2 IS TRUE;
19 +----------+-----------+
20 | 2 = TRUE | 2 IS TRUE |
21 +----------+-----------+
22 |        0 |         1 |
23 +----------+-----------+
24 1 row in set (0.00 sec)

Operador IS NOT

 1 IS NOT boolean_value
 2 
 3 Pruebas de un valor contra un valor booleano, donde valor 
 4 booleano puede ser TRUE, FALSE o UNKNOWN.
 5 
 6 MariaDB [(none)]> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
 7 +------------------+------------------+---------------------+
 8 | 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |
 9 +------------------+------------------+---------------------+
10 |                1 |                1 |                   0 |
11 +------------------+------------------+---------------------+
12 1 row in set (0.00 sec)
13 
14 MariaDB [(none)]> SELECT NULL IS NOT TRUE, NULL IS NOT FALSE;
15 +------------------+-------------------+
16 | NULL IS NOT TRUE | NULL IS NOT FALSE |
17 +------------------+-------------------+
18 |                1 |                 1 |
19 +------------------+-------------------+
20 1 row in set (0.00 sec)

Operador IS NOT NULL

1 Comprueba si un valor no es NULL.
2 
3 MariaDB [(none)]> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
4 +---------------+---------------+------------------+
5 | 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
6 +---------------+---------------+------------------+
7 |             1 |             1 |                0 |
8 +---------------+---------------+------------------+
9 1 row in set (0.00 sec)

Operador IS NULL

1 Comprueba si un valor es NULL.
2 
3 MariaDB [(none)]> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
4 +-----------+-----------+--------------+
5 | 1 IS NULL | 0 IS NULL | NULL IS NULL |
6 +-----------+-----------+--------------+
7 |         0 |         0 |            1 |
8 +-----------+-----------+--------------+
9 1 row in set (0.00 sec)

Operador ISNULL

 1 ISNULL(expr)
 2 
 3 Comprueba si un expresión es NULL. Si es NULL devuelve 1
 4 de lo contrario devuelve 0.
 5 MariaDB [(none)]> SELECT ISNULL(1+1);
 6 +-------------+
 7 | ISNULL(1+1) |
 8 +-------------+
 9 |           0 |
10 +-------------+
11 1 row in set (0.01 sec)
12 
13 MariaDB [(none)]> SELECT ISNULL(1/0);
14 +-------------+
15 | ISNULL(1/0) |
16 +-------------+
17 |           1 |
18 +-------------+
19 1 row in set (0.00 sec)

Operador LEAST

 1 LEAST(val1,val2,...)
 2 
 3 Con dos o más argumentos, retorna el argumento menor 
 4 (valor mínimo). Los argumentos se comparan usando las 
 5 siguientes reglas:
 6     Si el valor devuelto se utiliza en un contexto INTEGER 
 7     o todos los argumentos son valores enteros, se comparan 
 8     como enteros.
 9 
10     Si el valor devuelto se utiliza en un contexto REAL 
11     o todos los argumentos son valores de real, se comparan 
12     como reales.
13 
14     Si algún argumento es una cadena sensible a minúsculas 
15     y mayúsculas, la comparación de cadenas entre honora 
16     mayúsculas y minúsculas.
17 
18     En los demás casos, los argumentos se comparan como cadenas 
19     de caracteres insensibles a mayúsculas y minúsculas.
20 
21     Least() retorna NULL si algún argumento es NULL.
22 
23 MariaDB [(none)]> SELECT LEAST(2,0);
24 +------------+
25 | LEAST(2,0) |
26 +------------+
27 |          0 |
28 +------------+
29 1 row in set (0.00 sec)
30 
31 MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,767.0);
32 +---------------------------+
33 | LEAST(34.0,3.0,5.0,767.0) |
34 +---------------------------+
35 |                       3.0 |
36 +---------------------------+
37 1 row in set (0.00 sec)
38 
39 MariaDB [(none)]> SELECT LEAST('B','A','C');
40 +--------------------+
41 | LEAST('B','A','C') |
42 +--------------------+
43 | A                  |
44 +--------------------+
45 1 row in set (0.00 sec)
46 
47 MariaDB [(none)]> SELECT LEAST('B', 'a', 'A','C');
48 +--------------------------+
49 | LEAST('B', 'a', 'A','C') |
50 +--------------------------+
51 | a                        |
52 +--------------------------+
53 1 row in set (0.00 sec)
54 
55 MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,'',767.0);
56 +------------------------------+
57 | LEAST(34.0,3.0,5.0,'',767.0) |
58 +------------------------------+
59 | 0                            |
60 +------------------------------+
61 1 row in set (0.00 sec)
62 
63 MariaDB [(none)]> SELECT LEAST(34.0,3.0,5.0,NULL,767.0);
64 +--------------------------------+
65 | LEAST(34.0,3.0,5.0,NULL,767.0) |
66 +--------------------------------+
67 |                           NULL |
68 +--------------------------------+
69 1 row in set (0.00 sec)

Operador NOT BETWEEN

 1 expr NOT BETWEEN min AND max
 2 
 3 Esto es lo mismo que NOT (expr BETWEEN min y max).
 4 
 5 MariaDB [(none)]> SELECT 1 NOT BETWEEN 2 AND 3;
 6 +-----------------------+
 7 | 1 NOT BETWEEN 2 AND 3 |
 8 +-----------------------+
 9 |                     1 |
10 +-----------------------+
11 1 row in set (0.00 sec)
12 
13 MariaDB [(none)]> SELECT 'b' NOT BETWEEN 'a' AND 'c';
14 +-----------------------------+
15 | 'b' NOT BETWEEN 'a' AND 'c' |
16 +-----------------------------+
17 |                           0 |
18 +-----------------------------+
19 1 row in set (0.00 sec)
20 
21 MariaDB [(none)]> SELECT 1 NOT BETWEEN 1 AND NULL;
22 +--------------------------+
23 | 1 NOT BETWEEN 1 AND NULL |
24 +--------------------------+
25 |                     NULL |
26 +--------------------------+
27 1 row in set (0.00 sec)

Operador NOT IN

 1 expr NOT IN (value,...)
 2 
 3 Lo mismo que NOT (expr IN (val1, val2, ...)).
 4 
 5 MariaDB [(none)]> SELECT 2 NOT IN (0,3,5,7);
 6 +--------------------+
 7 | 2 NOT IN (0,3,5,7) |
 8 +--------------------+
 9 |                  1 |
10 +--------------------+
11 1 row in set (0.00 sec)
12 
13 MariaDB [(none)]> SELECT 'wefwf' NOT IN ('wee','wefwf','weg');
14 +--------------------------------------+
15 | 'wefwf' NOT IN ('wee','wefwf','weg') |
16 +--------------------------------------+
17 |                                    0 |
18 +--------------------------------------+
19 1 row in set (0.00 sec)
20 
21 MariaDB [(none)]> SELECT 1 NOT IN ('1', '2', '3');
22 +--------------------------+
23 | 1 NOT IN ('1', '2', '3') |
24 +--------------------------+
25 |                        0 |
26 +--------------------------+
27 1 row in set (0.00 sec)
28 
29 MariaDB [(none)]> SELECT NULL NOT IN (1, 2, 3);
30 +-----------------------+
31 | NULL NOT IN (1, 2, 3) |
32 +-----------------------+
33 |                  NULL |
34 +-----------------------+
35 1 row in set (0.00 sec)
36 
37 MariaDB [(none)]> SELECT 1 NOT IN (1, 2, NULL);
38 +-----------------------+
39 | 1 NOT IN (1, 2, NULL) |
40 +-----------------------+
41 |                     0 |
42 +-----------------------+
43 1 row in set (0.00 sec)
44 
45 MariaDB [(none)]> SELECT 5 NOT IN (1, 2, NULL);
46 +-----------------------+
47 | 5 NOT IN (1, 2, NULL) |
48 +-----------------------+
49 |                  NULL |
50 +-----------------------+
51 1 row in set (0.00 sec)
Obtenido de «http://wiki.cabal.mx/index.php?title=Curso_de_MariaDB_Desde_la_Consola/Operadores_de_Comparación&oldid=110401»