w3resource

SQL IN Operator

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 example

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 Expression: SQL IN  operator with text value.

Relational Algebra Tree:

Relational Algebra Tree: SQL IN  operator with text value.

Relational Algebra Expression:

Relational Algebra Expression: SQL IN  operator with text value.

Relational Algebra Tree:

Relational Algebra Tree: SQL IN  operator with text value.

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/in-operator/sql-in-operator.php