SQL IN Operator

Last update on October 08 2024 12:40:13 (UTC/GMT +8 hours)

IN operator

The IN operator checks a value within a set of values separated by commas and retrieve the rows from the table which are matching. The IN returns 1 when the search value present within the range otherwise returns 0.

Syntax:

SELECT [column_name... | expression ]
FROM [table_name]
{WHERE | HAVING | {AND | OR}} value [NOT] IN ({comp_value1, comp_value2[, ...] | subquery});

Parameters:

Name	Description
column_name	Name of the column of the table.
expression	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name	Name of the table
{WHERE \| HAVING \| {AND \| OR}} value	IN works with either the WHERE or the HAVING clause. You can also use AND or OR clause for multi-condition WHERE or the HAVING clause.
NOT	Used to exclude the defined multiple values in a WHERE clause condition.
comp_value1, comp_value2...\| subquery	List of comparative values within the parentheses or a subquery that returns one or more values of a compatible datatype of the main query.

DBMS Support:IN Operator

DBMS	Command
MySQL	Supported
PostgreSQL	Supported
SQL Server	Supported
Oracle	Supported

Example: SQL IN Operator

To know whether the search value 15 is present within the specified range from the DUAL table, the following SQL statement can be used :

SQL Code:


SELECT 15 IN (5, 10, 15, 20, 56, 69) 
-- Check if the value 15 is present in the list (5, 10, 15, 20, 56, 69)
FROM dual;
-- From the virtual table dual

Explanation:

SELECT 15 IN (5, 10, 15, 20, 56, 69): This is a SELECT statement that checks if the value 15 is present in the given list of values (5, 10, 15, 20, 56, 69).
FROM dual: This is used to provide a dummy table called "dual" in Oracle SQL. It has a single row, so it is often used in queries where no table is actually needed.

Visual Presentation :

SQL IN operator with text value

The checking value of IN operator can be a string or word or sentence. These values can also be checked within a set of values separated by commas and retrieve the rows containing these values.

Example:

Sample table: agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

Here we look for all agents in the agents table of inventory database who have a working area of the state of 'London', 'Mumbai' or 'Chennai'.

Here is the SQL statement:

SQL Code:


SELECT *
-- Selecting all columns (*) from the table
FROM agents
-- From the table named "agents"
WHERE working_area IN ('London', 'Mumbai', 'Chennai');
-- Where the value in the column "working_area" is either 'London', 'Mumbai', or 'Chennai'

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE working_area IN ('London', 'Mumbai', 'Chennai'): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "working_area" column is either 'London', 'Mumbai', or 'Chennai' should be included in the result set.

This statement can also be used like bellow:

SQL Code:


SELECT *
-- Selecting all columns (*) from the table
FROM agents
-- From the table named "agents"
WHERE working_area = 'London' OR working_area = 'Mumbai' OR working_area = 'Chennai';
-- Where the value in the column "working_area" is 'London', 'Mumbai', or 'Chennai'

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE working_area = 'London' OR working_area = 'Mumbai' OR working_area = 'Chennai': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "working_area" column is either 'London', 'Mumbai', or 'Chennai' should be included in the result set.

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION PHONE_NO        COUNTRY
---------- -------------------- -------------------- ---------- --------------- ----------
A003       Alex                 London                      .13 075-12458969
A010       Santakumar           Chennai                     .14 007-22388644
A002       Mukesh               Mumbai                      .11 029-12358964
A006       McDen                London                      .15 078-22255588

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation : SQL IN operator with text value

Example: SQL IN operator with text value

SQL IN operator with numeric value

How can a numeric value be searched within a list of supplying values using IN operator in a select statement?

Example:

Sample table: agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get data of all columns from the 'agents' table with the following condition -

1.'commission' for the agents will be any of .13, .14 and .12,

the following SQL statement can be used :

SQL Code:


SELECT *
-- Selecting all columns (*) from the table
FROM agents
-- From the table named "agents"
WHERE commission IN (.13, .14, .12);
-- Where the value in the column "commission" is either .13, .14, or .12

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission IN (.13, .14, .12): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is either .13, .14, or .12 should be included in the result set.

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION PHONE_NO        COUNTRY
---------- -------------------- -------------------- ---------- --------------- ------------
A003       Alex                 London                      .13 075-12458969
A001       Subbarao             Bangalore                   .14 077-12346674
A008       Alford               New York                    .12 044-25874365
A010       Santakumar           Chennai                     .14 007-22388644
A012       Lucida               San Jose                    .12 044-52981425
A005       Anderson             Brisban                     .13 045-21447739

SQL IN operator with boolean NOT

In the following example, we have discussed the usage of IN operator with the boolean operator NOT in a select statement.

Example:

strong>Sample table: agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' for the agents will be none of .13, .14, .12,

the following SQL statement can be used:

SQL Code:


SELECT *
-- Selecting all columns (*) from the table
FROM agents
-- From the table named "agents"
WHERE commission NOT IN (.13, .14, .12);
-- Where the value in the column "commission" is not (.13, .14, .12)

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission NOT IN (.13, .14, .12): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is not .13, .14, or .12 should be included in the result set.

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION PHONE_NO        COUNTRY
---------- -------------------- -------------------- ---------- --------------- ---------
A009       Benjamin             Hampshair                   .11 008-22536178
A007       Ramasundar           Bangalore                   .15 077-25814763
A011       Ravi Kumar           Bangalore                   .15 077-45625874
A002       Mukesh               Mumbai                      .11 029-12358964
A006       McDen                London                      .15 078-22255588
A004       Ivan                 Torento                     .15 008-22544166

Visual Presentation : SQL IN operator with boolean NOT

Example: SQL IN operator with boolean NOT

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.