w3resource logo


>SQL IN operator

SQL IN Operator

rating Average rating 10 out of 10. Total 1 users rated.

<<PreviousNext>>

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

Description

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 agentsWHERE working_area IN ('London', 'Mumbai', 'Chennai');

This statement can also be used like bellow :

SELECT *FROM agentsWHERE working_area='London'OR working_area='Mumbai'OR working_area='Chennai';

Output

Sql In operator using text value

SQL IN operator with numeric value

Description

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  agentsWHERE  commission  IN  (.13,.14,.12);

Output

Sql In operator using numeric value

SQL IN operator with boolean NOT

Description

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 agentsWHERE commission NOT IN (.13,.14,.12);

Output

Sql In operator with boolean Not

SQL Online Practice Editor :


Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database;



<<PreviousNext>>