MySQL - Between Operator

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

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