Between in SQL - Other Relational Operators

We have seen the relational operators: = (equal), <> (different),> (greater), <(less),> = (greater or equal), <= (less than or equal), is null / is not null ( whether a value is NULL or not).

Another relational operator is "between", they work with intervals of values.

Until now, to retrieve from the "books" table the books with a price greater than or equal to 20 and less than or equal to 40, we used 2 conditions joined by the logical operator "and":

 select * from books

  where price> = 20 and

  price <= 40;

We can use "between" and thus simplify the query:

 select * from books

  where price between 20 and 40;

We find out if the value of a given field (price) is between the specified minimum and maximum values ​​(20 and 40 respectively).

"between" means "between". Work with range of values.

This operator does not take "null" values ​​into account.

If we add the "not" operator before "between" the result is inverted, that is, records that are outside the specified range are retrieved. For example, we recover the books whose price is NOT between 20 and 30, that is, those less than 20 and greater than 30:

 select * from books

  where price not between 20 and 30;

We can specify a range of date type values ​​with "between":

 select * from books

  where edition between '01 / 05/2000 'and '01 / 05/2007';

So we use the "between" operator to reduce the "where" conditions.