SQL IN operator
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 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
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 agents WHERE commission IN (.13,.14,.12);
Output
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 agents WHERE commission NOT IN (.13,.14,.12);
Output
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
