Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

MySQL Not Between Operator

by suresh

The MySQL Not Between Operator returns the records whose values are not between the given two values or range. The MySQL Not between operator is exactly opposite to the Between Operator

For example, If you want to find the 2018 Sales. However, you might not be interested in April and May Sales, then use this MySQL Not Between with April and May as the Values.

MySQL Not Between Operator Syntax

The basic syntax of the MySQL Not Between operator is

SELECT Column_Names 
FROM Table_Name
WHERE Column_Value NOT BETWEEN Value1 AND Value2

The Not between operator displays the records that are not between the Value1 and Value2 Including them. It mean, Column_Value <= Value1 and Column_Value >= Value2.

MySQL Not Between example

This simple example shows the working functionality of MySQL Not Between on Numerical values.

In the first statement, 3.2 is not Between 1 and 3, and 22 is not between 1 and 9 so, it returns 1. In the next statement, 5 is between 1 and 7, and 2 is between 1 & 9, so it returns 0.

SELECT 3.2 NOT BETWEEN 1 AND 3, 22 NOT BETWEEN 1 AND 9;

SELECT 5 NOT BETWEEN 1 AND 7, 2 NOT BETWEEN 1 AND 9;

SELECT 2 NOT BETWEEN 3 AND 1, 7 NOT BETWEEN 9 AND 1;

OUTPUT

MySQL NOT BETWEEN Operator 1

In this MySQL Not Between Operator example, we explain to you how to use this Not Between operator on String data.

The first MySQL statement returns 1, because d is not between a and c, and t is not between u and g. Within the second statement, it converts the string 10 to int and tests whether 2 is not between 4 and 10.

SELECT 'd' NOT BETWEEN 'a' AND 'c', 't' NOT BETWEEN 'u' AND 'g';

SELECT 2 NOT BETWEEN 4 AND '10', 7 NOT BETWEEN 5 AND '10';

SELECT 2 NOT BETWEEN 5 AND 'Hello';

OUTPUT

MySQL NOT BETWEEN Operator 2

MySQL Not Between Operator On Numeric and String Data Example

For this MySQL Not Between operator demonstration, we use this Customer table. The following screenshot shows you the data present inside this table

MySQL NOT BETWEEN Operator 3

The following MySQL Not Between Operator query returns the Customers whose Income is not between 70000 and 90000. Or, whose Income is less than 70000 and greater than 90000.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Income NOT BETWEEN 70000 AND 90000;

OUTPUT

MySQL NOT BETWEEN Operator 4

This MySQL Not Between operator example returns the Customers whose First Name is Not between Gail and Peter. It returns the first names that start with before Alphabet G and after P

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE `First Name` NOT BETWEEN 'Gail' AND 'Peter';

TIP: We can also use a single character because this not between operator considers the first character as a reference.

OUTPUT

MySQL NOT BETWEEN Operator 5

MySQL Not Between Dates Example

In this MySQL Not Between Dates example, we return all the Customers whose Hire Date is not between 2009-01-01 and 2013-01-15

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE HireDate  NOT BETWEEN '2009-01-01 ' AND '2013-01-15';

OUTPUT

MySQL Dates NOT BETWEEN 6

Placed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy