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.

Sample table: publisher

Code:

-- This query selects specific columns from the 'publisher' table where the establishment year is between 1968 and 1975.
SELECT pub_name, country, pub_city, estd
-- This statement specifies the columns to be retrieved: 'pub_name', 'country', 'pub_city', and 'estd'.
FROM publisher
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'.
WHERE YEAR(estd) BETWEEN 1968 AND 1975;
-- This clause filters the rows to include only those where the year of the 'estd' column is between 1968 and 1975.

Explanation:

• The purpose of this SQL query is to retrieve information about publishers established between the years 1968 and 1975.


• SELECT pub_name, country, pub_city, estd: This part of the query specifies the columns to be selected from the 'publisher' table. It includes the publisher's name (pub_name), country (country), city (pub_city), and establishment date (estd).


• FROM publisher: This part specifies the table from which the data is being retrieved, which is the 'publisher' table.


• WHERE YEAR(estd) BETWEEN 1968 AND 1975: This clause filters the results to include only those rows where the establishment year (derived from the estd column) is between 1968 and 1975. The YEAR() function extracts the year part from the estd column, allowing for the comparison.

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.

Sample table: publisher

Code:

-- This query selects specific columns from the 'publisher' table where the establishment month is between February and August.
SELECT pub_name, country, pub_city, estd 
-- This statement specifies the columns to be retrieved: 'pub_name', 'country', 'pub_city', and 'estd'.
FROM publisher        
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'.
WHERE MONTH(estd) BETWEEN '02' AND '08';
-- This clause filters the rows to include only those where the month part of the 'estd' column is between February (02) and August (08).

Explanation:

• The purpose of this SQL query is to retrieve information about publishers that were established between the months of February and August, regardless of the year.


• SELECT pub_name, country, pub_city, estd: This part of the query specifies the columns to be selected from the 'publisher' table. It includes the publisher's name (pub_name), country (country), city (pub_city), and establishment date (estd).


• FROM publisher: This part specifies the table from which the data is being retrieved, which is the 'publisher' table.


• WHERE MONTH(estd) BETWEEN '02' AND '08': This clause filters the results to include only those rows where the month part of the estd column is between February (02) and August (08). The MONTH() function extracts the month part from the estd column, allowing for the comparison.

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.

Sample table: publisher

Code:

-- This query selects specific columns from the 'publisher' table where the establishment month is between May and September and the establishment year is between 1950 and 1975.
SELECT pub_name, country, pub_city, estd
-- This statement specifies the columns to be retrieved: 'pub_name', 'country', 'pub_city', and 'estd'.
FROM publisher          
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'.
WHERE MONTH(estd) BETWEEN '05' AND '09' 
-- This clause filters the rows to include only those where the month part of the 'estd' column is between May (05) and September (09).
AND YEAR(estd) BETWEEN 1950 AND 1975;
-- This additional clause further filters the rows to include only those where the year part of the 'estd' column is between 1950 and 1975.

Explanation:

• The purpose of this SQL query is to retrieve information about publishers that were established between the months of May and September, and between the years 1950 and 1975.


• SELECT pub_name, country, pub_city, estd: This part of the query specifies the columns to be selected from the 'publisher' table. It includes the publisher's name (pub_name), country (country), city (pub_city), and establishment date (estd).


• FROM publisher: This part specifies the table from which the data is being retrieved, which is the 'publisher' table.


• WHERE MONTH(estd) BETWEEN '05' AND '09': This clause filters the results to include only those rows where the month part of the estd column is between May (05) and September (09). The MONTH() function extracts the month part from the estd column, allowing for the comparison.


• AND YEAR(estd) BETWEEN 1950 AND 1975: This additional clause further filters the results to include only those rows where the year part of the estd column is between 1950 and 1975. The YEAR() function extracts the year part from the estd column, allowing for the comparison.

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.

Sample table: publisher

Code:

-- This query selects specific columns from the 'publisher' table where the establishment date is between January 1, 1950, and December 31, 1975.
SELECT pub_name, country, pub_city, estd
-- This statement specifies the columns to be retrieved: 'pub_name', 'country', 'pub_city', and 'estd'.
FROM publisher 
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'.
WHERE estd BETWEEN '1950-01-01' AND '1975-12-31';
-- This clause filters the rows to include only those where the 'estd' column is between January 1, 1950, and December 31, 1975.
   
   

Explanation:

• The purpose of this SQL query is to retrieve information about publishers that were established between January 1, 1950, and December 31, 1975.


• SELECT pub_name, country, pub_city, estd: This part of the query specifies the columns to be selected from the 'publisher' table. It includes the publisher's name (pub_name), country (country), city (pub_city), and establishment date (estd).


• FROM publisher: This part specifies the table from which the data is being selected, which is the 'publisher' table.


• WHERE estd BETWEEN '1950-01-01' AND '1975-12-31': This clause filters the results to include only those rows where the estd column (establishment date) is between January 1, 1950, and December 31, 1975. The BETWEEN operator is used to select values within a given range, inclusive of the specified start and end dates.

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)
 

Relational Algebra Expression:

Relational Algebra Tree:

Slideshow of MySQL Comparison Function and Operators

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