The SQL IN Operator

The SQL IN Operator is used 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 list. The list of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …).

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

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.

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 list to be tested against the expression. The IN operator returns TRUE if any of these values is found in the list, and FALSE if it is not.

IN Operator with 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

In this example, we are using the IN operator to specify multiple values in SELECT statement 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 VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as follows −

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 −

Example

The above query can also be done using OR operator. Following is an example −

SELECT * FROM CUSTOMERS 
WHERE NAME = ''Khilan'' OR NAME = ''Hardik'' OR NAME = ''Muffy'';

Output

IN Operator with 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, we 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 −

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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 −

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

IN Operator with NOT

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 list 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 displaying 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 −

IN Operator with Column Name

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 selecting the rows with the value ”2000” in the SALARY column −

SELECT * FROM CUSTOMERS WHERE 2000 IN (SALARY);

Output

This would produce the following result −

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 list cannot be included in the subquery.

Syntax

The basic syntax of the IN operator to specify a subquery 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 −