w3resource logo


>SQL IN operator

SQL IN Operator

<<PreviousNext>>

Seondary 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 value 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 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 a numeric value can 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 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 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



<<PreviousNext>>

Is this content useful for you?

Looking for some other tutorial?