MySQL - IN Operator

To use IN operator in MySQL table through JavaScript program, we need to execute the SQL statement with IN using the function named query() provided by mysql2 connector −

sql= "SELECT columns FROM table_name 
WHERE column_name IN (value1, value2, value3, ...)";  
con.query(sql);

To use IN operator in MySQL table through Java program, we need to execute the SQL statement with IN using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT COLUMN(S) FROM TABLE_NAME 
WHERE COLUMN_NAME IN(VALUE1, VALUE2, VALUE3,...)";
statement.executeQuery(sql);

To use IN operator in MySQL table through Python program, we need to execute the SQL statement with IN using the function named execute() provided by MySQL Connector/Python −

in_query = "SELECT column1, column2, ... FROM table_name 
WHERE column_name IN (value1, value2, value3, ...)"
cursorObj.execute(in_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 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 IN Operator
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages IN ( 250, 220, 170 );"
  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
  }
]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class InOperator {
    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 = "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))";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //now lets insert some records
            String sql1 = "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, 'MP', 4500.00 ), (7, 'Muffy', 24, 'Indore', 10000.00 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully....!");
            //display records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            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);
            }
            //let use IN operator with where clause to update records
            String sql3 = "UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27)";
            st.executeUpdate(sql3);
            //display table records after update
            String sql4 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql4);
            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);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}                                     

Output

The output obtained is as shown below −

Table created successfully...!
Records inserted successfully....!
Table records before update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00
Table records before update: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00          

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
in_query = f"""SELECT * FROM customers
WHERE id IN (2, 4, 6)"""
cursorObj.execute(in_query)
# Fetching all 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 −

(2, 'Khilan', 25, 'Kerala', Decimal('8000.00'))
(4, 'Chaital', 25, 'Mumbai', Decimal('1200.00'))
(6, 'Komal', 29, 'Vishakapatnam', Decimal('7000.00'))