NOT BETWEEN … AND …

Description

If expr is less than or equal to min and expr is greater than or equal to max, NOT BETWEEN returns true, otherwise it returns false.

Syntax

  1. > expr NOT BETWEEN min AND max

Examples

  1. > SELECT 2 NOT BETWEEN 1 AND 3, 2 NOT BETWEEN 3 and 1;
  2. +-----------------------+-----------------------+
  3. | 2 not between 1 and 3 | 2 not between 3 and 1 |
  4. +-----------------------+-----------------------+
  5. | false | true |
  6. +-----------------------+-----------------------+
  7. 1 row in set (0.00 sec)
  1. > create table t (id bigint unsigned, b int);
  2. > insert into t values(8894754949779693574,1);
  3. > insert into t values(8894754949779693579,2);
  4. > insert into t values(17790886498483827171,3);
  5. > select count(*) from t where id>=8894754949779693574 and id =17790886498483827171 order by 1 asc;
  6. +----------+
  7. | count(*) |
  8. +----------+
  9. | 0 |
  10. +----------+
  11. > select count(*) from t where id not between 8894754949779693574 and 17790886498483827171;
  12. +----------+
  13. | count(*) |
  14. +----------+
  15. | 3 |
  16. +----------+
  17. 1 row in set (0.00 sec)