SQL IN Operator

The logical operator IN in SQL is used with the WHERE statement to check if a column is equal to one of the values included in the given set of values. It is a simple method to check if a column is equal to a value OR another value OR another value and so on, without having to use the OR operator multiple times.
 

Syntax:

To search for all rows where the column “column_name” is equal to ‘value 1’ OR ‘value 2’ or ‘value 3’, you can use the following syntax:

SELECT column_name
FROM table
WHERE column_name IN ( value1, value2, value3, ... )

 
This syntax can be combined with the NOT operator to search for all lines that are not equal to one of the specified values.

SELECT column_name
FROM table
WHERE column_name NOT IN ( value1, value2, value3, ... )

 

 

Simplicity of the IN operator

The syntax used with the operator is simpler than using a succession of OR operators. To show this with an example, here are 2 queries that will return the same results, one uses the IN operator, while the other uses multiple OR.
 
Query with multiple OR :

SELECT name
FROM product
WHERE name = 'Keyboard' OR name = 'Mouse' OR name = 'RAM'

 
Equivalent query with the IN operator :

SELECT name
FROM product
WHERE name IN ( 'Keyboard', 'Mouse', 'RAM' )

 

Example:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | furniture |  148  | 2       |
|  105 | Gum      | furniture |  250  | 1       |
+------+----------+------------+-------+---------+

 

 
If we want to get the name records of “Keyboard” and “Mouse”, we can use the following query:

SELECT name
FROM product
WHERE name IN ( 'Keyboard', 'Mouse' )

 
Output:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
+------+----------+------------+-------+---------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More