SQL BETWEEN operator tutorial with MySql

SQL BETWEEN OPERATOR

BETWEEN is a useful operator to be used in the WHERE clause. You can use it to specify an inclusive range of values. It is frequently used with dates but can be used with string and numeric data as well.

SQL BETWEEN SYNTAX

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL BETWEEN EXAMPLE

We are going to use our table "authors" for our example:

With the following statement, we want to select the authors which were born between the 2nd of february 1971 and the 1st of January 1985:

SELECT * FROM eli.authors WHERE birthday BETWEEN '1971-02-02' AND '1985-01-01';

With the following result;

If you use the BETWEEN operator, you see that it provides exactly the same results as the "greater than or
equal to" (>=) and "less than or equal to" (<=) operators do. It is extremely important to remember that
the BETWEEN operator is inclusive.

---

You can use BETWEEN with data types other than dates, such as text;

After taking a look at our table "books";

We are going to select registers which have their "title" starting with the letter "a" to "s";

SELECT * FROM eli.books WHERE title BETWEEN 'a' and 's';

You will see all titles that start with "a, b, c, d...." and ..."r", the last letter before "s". You will not see the titles beginning with "s" however. A title composed of only the letter "s" would be returned in the results. Any title beginning with "s" and at least one other character is greater than "s" and therefore not within the range.

---

Using the NOT and BETWEEN Operators

You can also use the BETWEEN operator in conjunction with the NOT operator, in which case SQL selects a value that is not in the range specified

From our table "publisher";

We are going to select the "names" of the "publishers" which have a phone number which is not between "4999" and "5010";

SELECT name FROM eli.publisher where phone not between '4999' and '5010';

<< Previous

Next >>