MySQL NOT BETWEEN AND operator

Last update on April 04 2022 13:09:18 (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: 5.6

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.

Code:

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

Sample table: publisher

Sample 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)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-not-between-and-operator - php mysql examples | w3resource</title>
<meta name="description" content="example-not-between-and-operator - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers whose year of establishment is not between 1968 and 1975, along with their country, city and date of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th><th>Country</th><th>City</th><th>Date of establishment</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,country,pub_city,estd 
FROM publisher 
WHERE YEAR(estd) 
NOT BETWEEN 1968 AND 1975') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-not-between-and-operator</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT pub_name,country,pub_city,estd FROM publisher WHERE YEAR(estd) NOT BETWEEN 1968 AND 1975";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher</td>
<td>Country</td>
<td>City</td>
<td>Date of establishment</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("country")%></TD>
<TD><%=rs.getString("pub_city")%></TD>
<TD><%=rs.getString("estd")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

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.

Code:

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

Sample table: publisher

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.

Code:

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

Sample table: publisher

Sample 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.

Code:

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

Relational Algebra Expression:

Relational Algebra Tree:

Sample table: publisher

Sample 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)

Online Practice Editor:

Slideshow of MySQL Comparison Function and Operators

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