MySQL – Between Operator


MySQL – Between Operator

Table of content

”;


Previous

Next

MySQL Between Operator

The BETWEEN operator in MySQL is a logical operator provided by SQL, that is used to restrict the range from which the data values are to be retrieved. The retrieved values can be integers, characters, or dates.

You can use BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.

Let us understand in a better way by using the following example table −

between

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here,

  • value1 is the beginning value of the range.
  • value2 is the ending value of the range (inclusive).

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Let us insert some values into this table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table created is as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Now, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 −

SELECT * FROM CUSTOMERS 
WHERE AGE BETWEEN 20 AND 25;

Output

When we execute the above query, the output is obtained as follows −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

MySQL BETWEEN with IN Operator

The BETWEEN operator and the IN operator can be used together in a MySQL query, to select values that are within a specified range and also match with specified values.

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
AND column_name IN (list_of_values);

Example

In this example, we are selecting all the customers whose salary is between 4000 and 10000. In addition; we are only retrieving the customers who are living in MP and Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS 
WHERE SALARY BETWEEN 4000 AND 10000 
AND ADDRESS IN (''Hyderabad'', ''Bhopal'');

Output

The following is obtained −

ID NAME AGE ADDRESS SALARY
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00

MySQL BETWEEN with UPDATE statement

The UPDATE statement in MySQL is used to modify existing data in a database table. Using the BETWEEN operator in an UPDATE statement to update values within the specified range.

Example

Let us update the salaries of the customers whose age lies between 25 to 30 using the following query −

UPDATE CUSTOMERS 
SET SALARY = 10000 
WHERE AGE BETWEEN 20 AND 25;

Verification

Let us verify whether the salaries are updated or not using the following query −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 10000.00
3 Kaushik 23 Kota 10000.00
4 Chaitali 25 Mumbai 10000.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 10000.00
7 Muffy 24 Indore 10000.00

BETWEEN operator with DELETE statement

We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.

Example

Now, let us delete the customers whose age is between 18 and 20 using the DELETE command.

DELETE FROM CUSTOMERS 
WHERE AGE BETWEEN 20 AND 22;

Verification

Let us verify whether the specified aged employees are deleted or not using the following query −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

MySQL NOT BETWEEN Operator

The NOT BETWEEN operator in MySQL is a negation of the BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.

Syntax

Following is the syntax of the NOT BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

Consider the CUSTOMERS table to retrieve the details of customers whose age is not between 20 and 25 (numeric data) using the following query.

SELECT * FROM CUSTOMERS 
WHERE AGE NOT BETWEEN 20 AND 25;

Output

Following is the output −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00

NOT BETWEEN operator with IN operator

Like the BETWEEN operator, we can also use the NOT BETWEEN operator in combination with the IN operator. This is to select values that fall outside a range and also do not match with the specified values.

Example

In the following query, we are selecting the customers whose salary is NOT between 1000 and 5000. In addition; we are not retrieving the employees who are living in Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS 
WHERE SALARY NOT BETWEEN 1000 AND 5000 
AND ADDRESS NOT IN (''Bhopal'');

Output

Following is the output −

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
7 Muffy 24 Indore 10000.00

Between Operator Using Client Program

We can also apply the BETWEEN operator on a MySQL table using a client program.

Syntax

Following are the syntaxes of the Between Operator in MySQL table in various programming languages −

PHP
NodeJS
Java
Python

To execute the Between Operator in MySQL through a PHP program, we need to execute the SQL query with BETWEEN statement using the mysqli function named query() as −

$sql = "SELECT column1, column2, ... FROM table_name 
WHERE column_name BETWEEN value1 AND value2";
$mysqli->query($sql);

To execute the Between Operator in MySQL through a JavaScript program, we need to execute the SQL query with BETWEEN statement using the mysql2 function named query() as −

sql = " SELECT column_name(s) FROM table_name 
WHERE column_name BETWEEN value1 AND value2";
con.query(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the JDBC type 4 driver function named executeQuery() as −

String sql = "SELECT column1, column2, ... FROM table_name 
WHERE column_name BETWEEN value1 AND value2";
statement.executeQuery(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the MySQL Connector/Python function named execute() as −

between_query = "SELECT column1, column2, ... FROM table_name 
WHERE column_name BETWEEN value1 AND value2"
cursorObj.execute(between_query);

Example

Following are the implementations of this operation in various programming languages −

PHP
NodeJS
Java
Python 
$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",   
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");         
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close()

Output

The output obtained is as follows −

Table records:
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.000000    

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using BETWEEN Clause
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages BETWEEN 170 AND 300;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    id: 1,
    name: ''John'',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: ''Ram'',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: ''Jack'',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: ''Jill'',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 5,
    name: ''Zara'',
    work_date: 2007-06-05T18:30:00.000Z,
    daily_typing_pages: 300
  }
]        

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BetweenClause {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql);
            System.out.println("Table records before update: ");
            while(rs.next()) {
                String id = rs.getString("Id");
                String name = rs.getString("Name");
                String age = rs.getString("Age");
                String address = rs.getString("Address");
                String salary = rs.getString("Salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
            String sql1 = "UPDATE CUSTOMERS SET SALARY = 10000 WHERE AGE BETWEEN 20 AND 25";
            st.executeUpdate(sql1);
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records after update: ");
            while(rs.next()) {
                String id = rs.getString("Id");
                String name = rs.getString("Name");
                String age = rs.getString("Age");
                String address = rs.getString("Address");
                String salary = rs.getString("Salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Table records before update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.00
Table records after update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.0            

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
between_query = f"""
SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000;
"""
cursorObj.execute(between_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *