w3resource logo


>SQL IN operator

SQL IN Operator

<<PreviousNext>>

Seondary Nav

Description

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 other wise returns 0.

Syntax

SELECT [column_name... | expression ]
FROM [table_name]
WHERE [NOT] IN (valu_1,value_2.....value_n);  

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.
value_1,value_2... Values specified within the parentheses.

Example

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;  

SQL IN operator with text value

The checking value of IN operator can also 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

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

1. 'working_area' for the 'agents' are any of 'London' or 'Mumbai' or 'Chennai',

the following sql statement can be used :

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

SQL IN operator with numeric value

In the following we are discussing how a numeric value can be searched within a list of supplied value 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 we are discussing 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

See our Model Database;



<<PreviousNext>>

Looking for some other tutorial?