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:

DBMS Support:IN Operator

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) 
FROM dual;

Pictorial 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

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 *
FROM agents
WHERE working_area IN('London','Mumbai','Chennai');

This statement can also be used like bellow:

SQL Code:

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

Relational Algebra Expression:

Relational Algebra Tree:

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation : 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 :

SQL Code:

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:

SQL Code:

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

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.