EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL IN Operator
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL IN Operator

By Aanchal SharmaAanchal Sharma

MySQL IN Operator

Introduction to MySQL IN Operator

  • MySQL IN Operator used to retrieve the result set by checking whether a value from the set of literal values or provided by the subquery is matched or not.
  • Basically, this operator helps to determine the specific value if available in the result of subquery using the WHERE clause.
  • It fetches the set of columns from the table if a particular value is present in the list of values provided to match a conditional expression.
  • Thus, the MySQL IN operator like other MySQL operators allows comparing a value matching from the set of column values returned by a subquery statement.
  • If we want to get the rows as result from the Database table where a particular value should be present in a list of values available through conditional query then, we use MySQL IN operator.
  • We extract the rows which have matching values in the list of values in IN operator function from a particular value of column given using WHERE keyword in MySQL.

Syntax:

We have the following SQL syntax statement to illustrate the IN operator working and query in MySQL:

SELECT * FROM TableName WHERE (ColumnA) IN (SELECT query statement);

OR

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

SELECT ColumnA, ColumnB,.., FROM TableName WHERE (ColumnA or any expressional condition) IN (‘ValueA’, ‘ValueB’,…..);

Explaining the above syntax in brief:

  • We need to provide any column value as ColumnA or use any expression with IN operator in the query of WHERE clause.
  • In the IN operator list, we need to differentiate the values with a comma (,).
  • We can provide any subquery statement using the SELECT clause in MySQL with IN keyword together to filter the values from other tables and run the statement to get the required set of result rows from the table.
  • The result of IN operator will be 1 if the column value or the subquery expression result value matches any values present in the list of values. If it does not match then, the result of IN operator will be 0.

How IN Operator Works in MySQL?

As per the above syntax, MySQL IN operator provides us an equal value in the arguments. To use the IN Keyword it is combined with WHERE clause together. When this happens, the query executed only affects the records whose values are matched with the list of values available in the IN Keyword. IN keyword in MySQL helps to minimize the use of the OR clause.

Suppose we have all the constant values in the list, then in MySQL following operational steps are carried:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,584 ratings)
  • Firstly, it examines if the values are of the same data type as of the ColumnA or not. Also, evaluation is done for expression or subquery results.
  • Next, the values are sorted in a proper sequence.
  • Then, the value is searched based on the Binary Search Algorithm, which allows the SQL statement query using IN operator with a list of constant values to execute very speedily.
  • Otherwise, based on the rules in MySQL type conversion can take place to process the query.

Examples to Implement IN operator in MySQL

Let us now discuss the IN operator using some examples and learn the code to write the SQL query for it.

Example #1 – within, NOT IN and OR

We are using tables named ‘Customers’ and ‘Employees’. Below is the SQL statement using IN operator in the Customers table.

Customers Table:

Customers Table Example 1

Employees Table:

Employees Table Example 1

SELECT * FROM Customers WHERE City IN (‘Delhi’, ‘Mumbai’);

The above query with IN operator will fetch all the records from the table where the customers are from the cities provided in the IN keyword: Delhi and Mumbai.

Output:

MySQL IN Operator Example 1

If we use OR operator in the query above then, the result will be the same as above:

SELECT * FROM Customers WHERE City = ‘Delhi’ OR City = ‘Mumbai’;

Output:

MySQL IN Operator Example 1

But if we have multiple values in the list, then we need to separate each value with OR operators and the statement will be constructed very long. Therefore, to avoid such kind of issue and making the SQL syntax more proper and readable, the IN operator is introduced in MySQL. So, the IN keywords has helped to shorten the statement by listing values together separated by commas.

In reverse, if we want to get the result set which does not match the values in the list then, we use the following query with NOT IN operator in MySQL:

Query:

SELECT * FROM Customers WHERE City NOT IN (‘Delhi’, ‘Mumbai’);

Output:

MySQL IN Operator Example 1

You can see from the result above that NOT IN operator has fetched the rows of customers form the table that does not belong or related to the cities provided in the list: Delhi and Mumbai.

Example #2 – With Subquery

Generally, the IN operator is used with an inner query or subquery. It is done so because instead of passing a list of literal values with IN keyword, we can use a subquery which fetches the list of values from one or multiple tables and provides them as input to IN operator to match the value in the outer query and produce the result from the SQL statement.

In the following query, we have taken both tables Customers (CustNum, Name, City, Phone) and Employees (EmpNum, Profile, CustNum, Salary) to filter the result set from both the tables in MySQL:

Query:

SELECT CustNum,Name, City FROM Customers WHERE CustNum IN (SELECT CustNum FROM Employees where EmpNum > 202 ) ORDER BY CustNum;

Output:

Subquery Example 2

Here, the IN Operator has provided the result rows from Customers using subquery where the output of subquery from Employees is the values of the CustNum column which includes the employees having their salary greater than 5000.

Conclusion

  • Remember that as per the above IN operator syntax, if ColumnA value is not present in the table or subquery expression provides NULL value or if any values in the list are NULL, then the condition does not give proper comparability of values and thus, the result of IN operator will be NULL.
  • If we want to get a result where the value does not match the values in the list of values then, we can use NOT IN operator instead of IN.
  • Hence, along with the WHERE clause we can use IN operator in MySQL in other SQL queries or statements like UPDATE and DELETE.

Recommended Articles

This is a guide to MySQL IN Operator. Here we discuss the Introduction to MySQL IN Operator and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

  1. Introduction to MySQL Operators
  2. Top 23 MySQL String functions
  3. MySQL vs SQLite | Top 14 Comparisons
  4. MySQL Timestamp | How to Works?
  5. Guide to IF Statement in MySQL
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more