SQL IN Operator | Multiple Values in WHERE Condition

The SQL IN Operator allows us to specify multiple values in the WHERE Condition. It removes the need for multiple OR conditions in queries. Therefore, you can provide a list of values to search in the table. In this article, we will discuss the IN Operator in SQL. Also, we will discuss a few examples of using it for writing SQL Queries.

Syntax

SELECT column1, column2 … FROM table_name WHERE column IN (value1, value2 … valueN);

  • column1, column2 … : The list of columns to fetch from the table.
  • table_name: The table from which to fetch.
  • value1, value2 … valueN: The list of values to check for in the WHERE Condition

By using the IN Operator in SQL, the query returns the rows which match either one of the condition. Therefore, the above query is similar to:

SELECT column1, column2 … FROM table_name WHERE column = value1 OR column = value2 … column = valueN;

Also, you can use the IN operator in other queries like UPDATE and DELETE also.

Demo Table

For instance, consider the demo table to refer to all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Examples

For example, let’s consider a few examples of using the operator.

Example 1: SELECT IN List of Values

For instance, if we want to fetch rows with columns matching a list of values, we can use the IN operator in WHERE condition.

mysql> SELECT * FROM employees WHERE team IN ('Gamma', 'Beta');
+----+------+--------+------------+-------+
| id | name | salary | experience | team  |
+----+------+--------+------------+-------+
|  3 | John |  15000 |          3 | Beta  |
|  4 | Mary |  18000 |          4 | Gamma |
|  5 | Ross |  30000 |          9 | Beta  |
+----+------+--------+------------+-------+

In the above example, we select the rows which have the team as Gamma or Beta.

Example 2: SELECT NOT IN List of Values

Similarly, we can fetch the rows with columns not matching the list of values. We can use the NOT IN Operator for this.

mysql> SELECT * FROM employees WHERE team NOT IN ('Gamma', 'Beta');
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  26000 |          7 | Alpha |
+----+--------+--------+------------+-------+

In the above example, we fetch the rows which do not have the team as Gamma or Beta.

Example 3: UPDATE IN Condition

Similarly, you can use the IN Operator in SQL in UPDATE Queries also. Therefore, you can also update multiple rows using a single query. In the example below, we update the rows which have the team as Alpha or Beta.

UPDATE employees SET experience = experience + 1 WHERE team IN ('Alpha', 'Beta');
Query OK, 4 row affected (0.06 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Example 4: DELETE IN Condition

Also, we can delete multiple rows by specifying the SQL IN condition in the DELETE Query in SQL. In the example below, we delete the rows having the team as Alpha or Gamma.

DELETE FROM employees WHERE team IN ('Alpha', 'Gamma');
Query OK, 3 row affected (0.07 sec)

Example 5: SELECT IN Subquery

Also, you can specify a subquery to give the list of values for the IN operator in SQL. First, the inner subquery will be executed, and its result will be used as the list of values for the main query.

SELECT * FROM table1 WHERE column_name IN (SELECT column FROM table2);

Conclusion

In conclusion, we discussed the SQL IN Operator. You can use it when you need to fetch rows according to multiple values in SQL. Also, you can read more about it on the Official Microsoft Documentation. Additionally, you can also read about more SQL Queries on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *