MySQL IN Clause

Last Updated: 26 July, 2023

The IN clause in MySQL is used to specify multiple values in a WHERE clause, allowing us to retrieve rows that match any of the specified values. It is often used in combination with the SELECT statement.

Here's the basic syntax of the IN clause:

 SELECT column1, column2, ...
 FROM table_name
 WHERE column_name IN (value1, value2, value3, ...);

In the above syntax:

• column1, column2, ... refers to the columns you want to retrieve in the result.
• table_name is the name of the table you want to query.
• column_name is the column you want to compare against the list of values.
• (value1, value2, value3, ...) is the list of values you want to check against the column.

Here's an example to illustrate the usage of the IN clause.

Let's say we have a table called employees with columns ID, Name, Department, Age, Salary and JoiningDate.

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

We want to retrieve all the employees table records whose department is either 'Sales', 'Marketing', or 'HR'. The SQL query would look like this:

  SELECT *
  FROM employees
  WHERE Department IN ('Sales', 'Marketing', 'HR');

Output the query

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  +----+-----------+------------+--------+------------+---------------+