Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

MySQL IN Operator

by suresh

The MySQL IN Operator is used to restrict the total number of rows returned by the SELECT Statement. The MySQL IN Operator checks the given expression against the Values inside the IN operator. If there is at least one match, then SELECT Statement returns the records

Please use MySQL IN Operator to check an expression or condition against Multiple values.

MySQL IN Operator Syntax

The basic syntax of the IN operator in MySQL can be written as:

SELECT [Column Names] 
FROM [Source]
WHERE [Column_Name] IN (Value1,...., ValueN)

-- Use OR to write the above statement
SELECT [Column Names]
FROM [Source]
WHERE [Column_Name] = Value1 OR [Column_Name] = ValueN

For this MySQL IN Operator example, We are using the below-shown data

MySQL IN Operator 4

MySQL IN Operator Example

In this example, we are checking for numeric values using this in operator.

SELECT 3 IN (1, 2, 3, 4, 5);

SELECT 6 IN (1, 2, 3, 4, 5);

SELECT 3.5 IN (1, 2, 3, 4, 5);

OUTPUT

MySQL IN Operator 1

Let me check for string data using in operator. Within the third statement, we used mixed values inside an in operator. In real-time, this might give strange results, so always use a single data type inside an in operator.

SELECT 'abc' IN ('xyz', 'hi', 'abc', 'ab');

SELECT 'a' IN ('xyz', 'hi', 'abc', 'ab');

SELECT 4 IN (1, 3, 4, '5');

OUTPUT

MySQL IN Operator 2

Instead of using Mixed values, we have to use any single MySQL type. So, the SELECT Statement gives an accurate result.

SELECT 4 IN ('1',' 3', '4', '5');

SELECT (10, 20) IN ((1,2), (10, 20), (100, 200));

SELECT 3 IN (1, 3, NULL, 4);

OUTPUT

MySQL IN Operator 3

MySQL IN Practical Examples

The following MySQL in operator query finds the Customers whose Income is either 80000 or 90000

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Income IN (80000, 90000);

OUTPUT

MySQL IN Numeric Values 5

For better understanding, Let me show you one more example. The following query returns the Customers whose Income is either 50000, 80000, or 90000

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Income IN (50000, 80000, 90000);

OUTPUT

MySQL IN Numeric Values 6

MySQL IN String Example

This time, we are using the String data. The following In operator query find the Customers whose Qualification in either Bachelors or Masters Degree.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Qualification IN ('Bachelors', 'Masters Degree');

OUTPUT

MySQL IN String 7

MySQL IN Order By Clause

You can also use Order By clause along with In operator. This query returns Customers whose Qualification in either Bachelors, Masters Degree, or Education. Next, Order By clause sorts the result based on the Income in ascending order.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Qualification IN ('Bachelors', 'Masters Degree', 'Education')
ORDER BY Income;

OUTPUT

MySQL IN Order By 8

Placed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy