w3resource

MySQL BETWEEN .... AND operator

BETWEEN .... AND operator

MySQL BETWEEN AND operator checks whether a value is within a range.

Syntax:

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise, it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise, type conversion takes place according to the rules

MySQL Version: 8.0

Example: MySQL BETWEEN .... AND operator

The following MySQL statement will fetch the rows from the table publisher which was established between the year 1968 and 1975.

Code:

SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE YEAR(estd) BETWEEN 1968 AND 1975;

Sample table: publisher


Output:

mysql> SELECT pub_name,country,pub_city,estd
    -> FROM publisher
    -> WHERE YEAR(estd) BETWEEN 1968 AND 1975;
+-------------------------+-----------+----------+------------+
| pub_name                | country   | pub_city | estd       |
+-------------------------+-----------+----------+------------+
| Jex Max Publication     | USA       | New York | 1969-12-25 | 
| New Harrold Publication | Australia | Adelaide | 1975-09-05 | 
| Mountain Publication    | USA       | Houstan  | 1975-01-01 | 
+-------------------------+-----------+----------+------------+
3 rows in set (0.01 sec)

Example: BETWEEN - AND operator with MONTH()

The following MySQL statement will fetch the rows from the table publisher which established between the month February and August.

Code:

SELECT pub_name,country,pub_city,estd 
FROM publisher        
WHERE MONTH(estd) BETWEEN '02' and '08';

Sample table: publisher


Output:

mysql> SELECT pub_name,country,pub_city,estd 
    -> FROM publisher        
    -> WHERE MONTH(estd) BETWEEN '02' and '08';
+------------------------------+---------+-----------+------------+
| pub_name                     | country | pub_city  | estd       |
+------------------------------+---------+-----------+------------+
| Ultra Press Inc.             | UK      | London    | 1948-07-10 | 
| Pieterson Grp. of Publishers | UK      | Cambridge | 1950-07-15 | 
+------------------------------+---------+-----------+------------+
2 rows in set (0.00 sec)

Example: BETWEEN - AND operator using logical AND

The following MySQL statement will fetch the rows from the table publisher which established between the month May and September and year between 1950 and 1975.

Code:

SELECT pub_name,country,pub_city,estd
FROM publisher          
WHERE MONTH(estd) BETWEEN '05' AND '09' 
AND YEAR(estd) BETWEEN 1950 AND 1975; 

Sample table: publisher


Output:

mysql> SELECT pub_name,country,pub_city,estd
    -> FROM publisher          
    -> WHERE MONTH(estd) BETWEEN '05' AND '09' 
    -> AND YEAR(estd) BETWEEN 1950 AND 1975;
+------------------------------+-----------+-----------+------------+
| pub_name                     | country   | pub_city  | estd       |
+------------------------------+-----------+-----------+------------+
| New Harrold Publication      | Australia | Adelaide  | 1975-09-05 | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 1950-07-15 | 
+------------------------------+-----------+-----------+------------+
2 rows in set (0.03 sec)
 

Example: BETWEEN - AND operator with a date range

The following MySQL statement will fetch the rows from the table publisher which estd between the specified dates.

Code:

SELECT pub_name,country,pub_city,estd
   FROM publisher 
   WHERE estd BETWEEN '1950-01-01' AND '1975-12-31';
   
   

Relational Algebra Expression:

Relational Algebra Expression: MySQL BETWEEN - AND operator with a date range.

Relational Algebra Tree:

Relational Algebra Tree: MySQL BETWEEN - AND operator with a date range.

Sample table: publisher


Output:

mysql> SELECT pub_name,country,pub_city,estd
    ->    FROM publisher 
    ->    WHERE estd BETWEEN '1950-01-01' AND '1975-12-31';
+------------------------------+-----------+-----------+------------+
| pub_name                     | country   | pub_city  | estd       |
+------------------------------+-----------+-----------+------------+
| Jex Max Publication          | USA       | New York  | 1969-12-25 | 
| New Harrold Publication      | Australia | Adelaide  | 1975-09-05 | 
| Mountain Publication         | USA       | Houstan   | 1975-01-01 | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 1950-07-15 | 
+------------------------------+-----------+-----------+------------+
4 rows in set (0.00 sec)
 

Slideshow of MySQL Comparison Function and Operators

Previous: Introduction of MySQL functions and operators
Next: COALESCE()



Follow us on Facebook and Twitter for latest update.