w3resource logo


>SQL IN operator

SQL IN Operator

Secondary Nav

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 :

SELECT 15 IN (5,10,15,20,56,69) 
FROM dual;  

Pictorial 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

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 :

SELECT *
FROM agents
WHERE working_area IN('London','Mumbai','Chennai');

This statement can also be used like bellow :

SELECT *
FROM agents
WHERE working_area='London' OR working_area='Mumbai' OR working_area='Chennai';

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

Pictorial 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

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 :

SELECT  *
FROM  agents
WHERE  commission  IN  (.13,.14,.12);

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 :
Sample table : agents

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 :

SELECT *
FROM agents
WHERE commission NOT IN (.13,.14,.12);

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

Pictorial Presentation : SQL IN operator with boolean NOT

Example: SQL IN operator with boolean NOT