Comparisons

Comparisons

Comparison Operators, IN, and BETWEEN

Comparisons (=, <> or !=, <, <=, >, >=) return 1, 0 or NULL; <=> is the NULL-safe equal, and IS [NOT] TRUE, FALSE or UNKNOWN tests a truth value. x BETWEEN a AND b means x >= a AND x <= b, IN tests a list, and row constructors compare left to right, the way a composite index sorts. The logical operators are AND, OR, NOT and XOR.

Comparison, IN, BETWEEN, row constructors and XOR on the reviews tableSQL

SELECT id, rating, body IS NULL AS no_text, rating <=> 4 AS is4,
       rating BETWEEN 3 AND 4 AS mid, product_id IN (1, 6) AS in_list,
       (product_id, rating) > (2, 3) AS row_gt, (rating > 3) XOR (body IS NULL) AS one_of,
       customer_id NOT IN (3, NULL) AS not_in_null
FROM reviews WHERE customer_id IN (1, 2);

Output

+----+--------+---------+-----+-----+---------+--------+--------+-------------+
| id | rating | no_text | is4 | mid | in_list | row_gt | one_of | not_in_null |
+----+--------+---------+-----+-----+---------+--------+--------+-------------+
|  1 |      5 |       0 |   0 |   0 |       1 |      0 |      1 |        NULL |
|  5 |      4 |       1 |   1 |   1 |       1 |      1 |      0 |        NULL |
|  2 |      4 |       0 |   1 |   1 |       0 |      1 |      1 |        NULL |
|  3 |      3 |       0 |   0 |   1 |       0 |      1 |      0 |        NULL |
+----+--------+---------+-----+-----+---------+--------+--------+-------------+

No row belongs to customer 3, yet NOT IN (3, NULL) is NULL for all of them, so a WHERE with it keeps nothing: the classic NOT IN (SELECT ...) bug (IN, ANY, ALL, and EXISTS). Under utf8mb4_0900_ai_ci, 'abc' = 'ABC' is 1, but, because the collation is NO PAD, 'abc' = 'abc ' is 0. &&, || and ! raise deprecation warning 1287; write AND, OR and NOT.

MENU