SQL - IN Operator

The SQL IN is a logical operator that allows us to specify multiple values or sub query in the WHERE clause.

It returns all rows in which the specified column matches one of the values in the pst. The pst of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …).

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values.

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. Instead of multiples OR statements we can use the IN operator.

The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions.

Syntax

The basic syntax of the SQL IN operator to specify multiple values is as follows −

WHERE column_name IN (value1, value2, value3, ...);

Where,

value1, value2, value3, … are the values in the pst to be tested against the expression. The IN operator returns TRUE if any of these values is found in the pst, and FALSE if it is not.

Using IN operator in SELECT Statement

We can use the SQL IN operator to specify multiple values in a WHERE clause, and we can also use it in a SELECT statement to retrieve data that matches any of the specified values.

Here, we are using the IN operator to specify multiple values in SELECT statement.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Suppose based on the above table we want to display records with NAME equal to ‘Khilan’, ‘Hardik’ and ‘Muffy’ (string values). This can be achieved using IN operator as follows −

select * from CUSTOMERS WHERE NAME IN ( Khilan ,  Hardik ,  Muffy );

Output

The result obtained is as follows −

+----+--------+-----+---------+----------+
| ID | NAME   | AGE | ADDRESS | SALARY   |
+----+--------+-----+---------+----------+
|  2 | Khilan |  25 | Delhi   |  1500.00 |
|  5 | Hardik |  27 | Bhopal  |  8500.00 |
|  7 | Muffy  |  24 | Indore  | 10000.00 |
+----+--------+-----+---------+----------+

Note − We cannot use the wildcard characters % , _ , etc. with the string values. The above query can also be done using OR operator as follows −

select * from CUSTOMERS WHERE NAME =  Khilan  OR NAME =  Hardik  OR NAME =  Muffy ;

Output

+----+--------+-----+---------+----------+
| ID | NAME   | AGE | ADDRESS | SALARY   |
+----+--------+-----+---------+----------+
|  2 | Khilan |  25 | Delhi   |  1500.00 |
|  5 | Hardik |  27 | Bhopal  |  8500.00 |
|  7 | Muffy  |  24 | Indore  | 10000.00 |
+----+--------+-----+---------+----------+

Using IN operator in UPDATE statement

We can also use the SQL IN operator in an UPDATE statement to update rows that match any of the specified values in a WHERE clause. The UPDATE statement is used to modify existing data in a database table.

Example

Here, we are using the IN operator to specify multiple values in the UPDATE statement and updating the CUSTOMERS table previously created. Here, are changing the records of the customers with age ‘25’ or ‘27’ and updating the age value to ‘30’ −

update customers set AGE = 30 where AGE IN (25, 27);

Output

We get the following result. We can observe that the age of 3 customers has been modified −

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the Customers table −

select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  30 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  30 | Mumbai    |  6500.00 |
|  5 | Hardik   |  30 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

As we can see in the above table, the AGE of ‘Khilan’, ‘Chaitap’ and ‘Hardik’ has been updated to ‘30’.

Using IN operator with NOT operator

To negate a condition, we use the NOT operator. The SQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause. In other words, the absence of a pst from an expression will be checked.

Syntax

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2, …);

Example

Now, we are trying to display all the records from the CUSTOMERS table, where the AGE is NOT equal to ‘25’, ‘23’ and ‘22’ −

select * from CUSTOMERS
WHERE AGE NOT IN (25, 23, 22);

Output

We obtain the result as given below −

+----+--------+-----+-----------+----------+
| ID | NAME   | AGE | ADDRESS   | SALARY   |
+----+--------+-----+-----------+----------+
|  1 | Ramesh |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik |  27 | Bhopal    |  8500.00 |
|  7 | Muffy  |  24 | Indore    | 10000.00 |
+----+--------+-----+-----------+----------+

Using IN operator with column

We can also use the SQL IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

In the below query, we are trying to select the rows with the value of thSALARY column −

select * from CUSTOMERS
WHERE 2000 IN (SALARY);

Output

This would produce the following result −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

Using subquery with IN operator

We can use the subquery with the IN operator that is used to return records from the single column. This means that more than one column in the SELECT column pst cannot be included in the subquery.

Syntax

The basic syntax of the IN operator to specify a query is as follows −

WHERE column_name IN (subquery);

Where,

Subquery − This is the SELECT statement that has a result set to be tested against the expression. The IN condition evaluates to true if any of these values match the expression.

Example

In the query given below we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

select * from CUSTOMERS
WHERE NAME IN (SELECT NAME from CUSTOMERS WHERE SALARY > 2000);

Output

This will produce the following result −

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitap |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+