MySQL NOT BETWEEN AND operator

Last update on May 24 2024 10:12:50 (UTC/GMT +8 hours)

NOT BETWEEN AND operator

MySQL NOT BETWEEN AND operator checks whether a value is not present between a starting and a closing expression.

Syntax:

expr NOT BETWEEN min AND max

If expr is not greater than or equal to min and expr is not less than or equal to max, BETWEEN returns 1, otherwise, it returns 0.

MySQL Version: 8.0

Example: MySQL NOT BETWEEN AND operator

The following MySQL statement will fetch the rows from the table publisher which established before 1968 or after 1975. The NOT operator is used to exclude the publishers which ware established within the given period.

Sample table: publisher

Code:


-- This SQL query selects the name, country, city, and establishment year of publishers
-- Explanation: The query retrieves information from the 'publisher' table, filtering out publishers established between 1968 and 1975.
SELECT pub_name, country, pub_city, estd  -- Selecting specific columns pub_name, country, pub_city, and estd from the 'publisher' table
FROM publisher  -- Specifying the table 'publisher' from which to retrieve the data
WHERE YEAR(estd) NOT BETWEEN 1968 AND 1975;  -- Filtering the rows based on the establishment year (estd), selecting rows where the year is not between 1968 and 1975

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'publisher' table: pub_name, country, pub_city, and estd.

The FROM clause specifies the table from which the data is to be retrieved, which is 'publisher' in this case.

The WHERE clause is used to filter the rows based on a condition.

The YEAR() function extracts the year from the estd column.

The NOT BETWEEN operator selects rows where the establishment year is not within the specified range (1968 to 1975).

Output:

mysql> SELECT pub_name,country,pub_city,estd
    -> FROM publisher
    -> WHERE YEAR(estd)  NOT BETWEEN 1968 AND 1975;
+------------------------------+---------+-----------+------------+
| pub_name                     | country | pub_city  | estd       |
+------------------------------+---------+-----------+------------+
| BPP Publication              | India   | Mumbai    | 1985-10-01 | 
| Ultra Press Inc.             | UK      | London    | 1948-07-10 | 
| Summer Night Publication     | USA     | New York  | 1990-12-10 | 
| Pieterson Grp. of Publishers | UK      | Cambridge | 1950-07-15 | 
| Novel Publisher Ltd.         | India   | New Delhi | 2000-01-01 | 
+------------------------------+---------+-----------+------------+
5 rows in set (0.04 sec)

Example: MySQL NOT BETWEEN AND operator with MONTH()

The following MySQL statement will fetch the rows from the table publisher which was established before the month February or after the month August.

Sample table: publisher

Code:


-- This SQL query selects the name, country, city, and establishment year of publishers
-- Explanation: The query retrieves information from the 'publisher' table, filtering out publishers established in the months outside of February to August.
SELECT pub_name, country, pub_city, estd  -- Selecting specific columns pub_name, country, pub_city, and estd from the 'publisher' table
FROM publisher  -- Specifying the table 'publisher' from which to retrieve the data
WHERE MONTH(estd) NOT BETWEEN '02' AND '08';  -- Filtering the rows based on the month of establishment (estd), selecting rows where the month is not between February and August

Example : MySQL NOT BETWEEN AND operator with logical AND

The following MySQL statement will fetch the rows from the table publisher which was established before the month May or after September and year of establishment is not in the period 1950 to 1975.

Sample table: publisher

Code:


-- This SQL query selects the name, country, city, and establishment year of publishers
-- Explanation: The query retrieves information from the 'publisher' table, filtering out publishers established outside of the months May to September and years outside of 1950 to 1975.
SELECT pub_name, country, pub_city, estd  -- Selecting specific columns pub_name, country, pub_city, and estd from the 'publisher' table
FROM publisher  -- Specifying the table 'publisher' from which to retrieve the data
WHERE MONTH(estd) NOT BETWEEN '05' AND '09'  -- Filtering the rows based on the month of establishment (estd), selecting rows where the month is not between May and September
AND YEAR(estd) NOT BETWEEN 1950 AND 1975;  -- Further filtering based on the year of establishment, selecting rows where the year is not between 1950 and 1975

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'publisher' table: pub_name, country, pub_city, and estd.

The FROM clause specifies the table from which the data is to be retrieved, which is 'publisher' in this case.

The WHERE clause is used to filter the rows based on multiple conditions.

The MONTH() function extracts the month from the estd column.

The YEAR() function extracts the year from the estd column.

The NOT BETWEEN operator is used in conjunction with MONTH() to select rows where the month of establishment is not within the specified range ('05' for May to '09' for September).

Similarly, the NOT BETWEEN operator is used in conjunction with YEAR() to select rows where the year of establishment is not within the specified range (1950 to 1975).

Output:

mysql> SELECT pub_name,country,pub_city,estd 
    -> FROM publisher 
    -> WHERE MONTH(estd)  NOT BETWEEN '02' and '08';
+--------------------------+-----------+-----------+------------+
| pub_name                 | country   | pub_city  | estd       |
+--------------------------+-----------+-----------+------------+
| Jex Max Publication      | USA       | New York  | 1969-12-25 | 
| BPP Publication          | India     | Mumbai    | 1985-10-01 | 
| New Harrold Publication  | Australia | Adelaide  | 1975-09-05 | 
| Mountain Publication     | USA       | Houstan   | 1975-01-01 | 
| Summer Night Publication | USA       | New York  | 1990-12-10 | 
| Novel Publisher Ltd.     | India     | New Delhi | 2000-01-01 | 
+--------------------------+-----------+-----------+------------+
6 rows in set (0.03 sec)

Example : MySQL NOT BETWEEN AND operator on a date range

The following MySQL statement will fetch the rows from the table publisher which 'established date' before 1st January 1950 or after 31st December 1975.

Sample table: publisher

Code:


-- This SQL query selects the name, country, city, and establishment year of publishers
-- Explanation: The query retrieves information from the 'publisher' table, filtering out publishers established outside of the range from January 1, 1950, to December 31, 1975.
SELECT pub_name, country, pub_city, estd  -- Selecting specific columns pub_name, country, pub_city, and estd from the 'publisher' table
FROM publisher  -- Specifying the table 'publisher' from which to retrieve the data
WHERE estd NOT BETWEEN '1950-01-01' AND '1975-12-31';  -- Filtering the rows based on the establishment year (estd), selecting rows where the year is not between 1950 and 1975

Explanation:

The SELECT statement is used to specify the columns to be retrieved from the 'publisher' table: pub_name, country, pub_city, and estd.

The FROM clause specifies the table from which the data is to be retrieved, which is 'publisher' in this case.

The WHERE clause is used to filter the rows based on a condition.

The NOT BETWEEN operator is used to select rows where the establishment year (estd) is not within the specified range ('1950-01-01' for January 1, 1950, to '1975-12-31' for December 31, 1975).

Output:

mysql> SELECT pub_name,country,pub_city,estd 
    -> FROM publisher             
    -> WHERE estd NOT BETWEEN '1950-01-01' AND '1975-12-31';
+--------------------------+---------+-----------+------------+
| pub_name                 | country | pub_city  | estd       |
+--------------------------+---------+-----------+------------+
| BPP Publication          | India   | Mumbai    | 1985-10-01 | 
| Ultra Press Inc.         | UK      | London    | 1948-07-10 | 
| Summer Night Publication | USA     | New York  | 1990-12-10 | 
| Novel Publisher Ltd.     | India   | New Delhi | 2000-01-01 | 
+--------------------------+---------+-----------+------------+
4 rows in set (0.03 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Slideshow of MySQL Comparison Function and Operators

Previous: LIKE
Next: NOT EQUAL OPERATOR(<>,!=)