Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

IN Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

IN Operator
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 
PHPNodeJSJavaPython
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);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$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      
﻿
﻿
﻿
﻿
﻿

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

IN Operator

MySQL In Operator

Syntax

Example

Output

The IN Operator in UPDATE statement

Example

Verification

MySQL NOT IN operator

Syntax

Example

Output

Comparing Values Using IN Operator

Example

Output

MySQL Subquery with IN operator

Syntax

Example

Output

In Operator Using Client Program

Syntax

Example

Output