mysql

Khóa học miễn phí MySQL – IN Operator nhận dự án làm có lương

Posted on by user

MySQL – IN Operator

Table of content

MySQL In Operator

The IN operator in MySQL is a logical operator that allows us to check whether the values in a database are present in a list of values specified in the SQL statement.

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. It returns all rows in which the specified column value matches any one of the values in the list.

The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions.

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. You can use IN clause to replace many OR conditions

Syntax

Following is the basic syntax of IN operator −

WHERE COLUMN_NAME IN (value1, value2, value3,....);

Example

To understand IN clause, let us first create a table named CUSTOMERS, using the following CREATE TABLE statement −

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

Now, insert the following records using the INSERT statement −

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 will be created 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

Using the following query, retrieve the records with the names ‘Khilan’, ‘Hardik’, ‘Muffy’, from the CUSTOMERS table −

SELECT * FROM CUSTOMERS 
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

Output

The output obtained is as follows −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

The IN Operator in UPDATE statement

The MySQL UPDATE statement is used to modify existing data in a database table. So, we can also use the IN operator in an UPDATE statement (as a filter) to update existing rows.

Example

In this example, let us update the records of the customers with age ‘25’ or ‘27’ by setting their value to ‘30’ −

UPDATE CUSTOMERS 
SET AGE = 30 WHERE AGE IN (25, 27);

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Use the following query to display the updated records in the CUSTOMERS table −

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

MySQL NOT IN operator

To negate a condition, we use the NOT operator. The MySQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause.

In other words, the absence of a list from an expression will be checked.

Syntax

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2,...);

Example

Now, we are trying to display all the records from the CUSTOMERS table, where the AGE is NOT equal to ”25”, ”23” and ”22” −

SELECT * FROM CUSTOMERS 
WHERE AGE NOT IN (25, 23, 22);

Output

The output is obtained as −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

Comparing Values Using IN Operator

We can also use the IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

In the below query, we are trying to select the rows with the values containing SALARY column −

SELECT * FROM CUSTOMERS 
WHERE 2000 IN (SALARY);

Output

The following output is obtained −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00

MySQL Subquery with IN operator

We can use a subquery with the IN operator to return records from a single column. This means that more than one column in the SELECT column list cannot be included in the subquery specified.

Syntax

The basic syntax of the IN operator to specify a query is as follows −

WHERE column_name IN (subquery);

Example

In the query given below we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

SELECT * FROM CUSTOMERS 
WHERE NAME IN (
   SELECT NAME FROM CUSTOMERS 
   WHERE SALARY > 2000
);

Output

The following output is obtained −

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

In Operator Using Client Program

We can execute IN operator using a client program, in addition to executing it directly in the MySQL server.

Syntax

Following are the syntaxes of the IN Operator using various programming languages −

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

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME 
WHERE COLUMN_NAME IN (VALUE1, VALUE2, ...)";
$mysqli->query($sql);

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

Example

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

$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 tutorials_tbl WHERE tutorial_author IN(''John'', ''Sanjay'', ''Mahesh'')";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",   
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("n");         
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 3, Title: JAVA Tutorial, Author: Sanjay, S_date 2007-05-21
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26      

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

mysql-useful-functions.htm

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user