IN ANY SOME ALL operators in PL-SQL

IN ANY SOME ALL operators in PL-SQL – Learn about in, Any, Some, All operators usage in PL-SQL  with example Programs.

Let’s Create a Student table (std) with column names as (STDNO , SNAME, JOINDATE, FEE,EPTNO)

CREATE TABLE std(STDNO NUMBER(4,0),
SNAME VARCHAR2(10 BYTE),
JOINDATE DATE,
FEE NUMBER(7,2),
DEPTNO NUMBER(2,0));

Insert the following data into std Table

STDNO	SNAME	JOINDATE	FEE	DEPTNO
7369	SMITH	17-DEC-80 12.00.00 AM	13000	1
7499	ALLEN	20-FEB-81 12.00.00 AM	16000	3
7521	WARD	22-FEB-81 12.00.00 AM	12500	3
7566	JONES	02-APR-81 12.00.00 AM	2975	2
7654	MARTIN	28-SEP-81 12.00.00 AM	12500	3
7698	BLAKE	01-MAY-81 12.00.00 AM	28500	3
7844	TURNER	08-SEP-81 12.00.00 AM	1500	3

IN OPERATOR

• The IN operator in SQL is similar to multiple OR operators using in a where condition with SQL statement.

Syntax 

WHERE  [EXPRESSION] IN (VALUE1, VALUE2...., VALUE N)

the above syntax is very similar to below Sntax with  OR operators in WHERE  condition

WHERE  [EXPRESSION= VALUE1] OR [EXPRESSION= VALUE2] OR... [EXPRESSION= VALUEN]

Example query using IN operator

SELECT * FROM STD WHERE FEE IN (13000 , 12500);

Example query using OR operator

SELECT * FROM STD WHERE FEE=13000 OR FEE=12500;

Note:

IN operator is similar =ANY operator in SQL.

Example query using =ANY operator

SELECT * FROM STD WHERE FEE = ANY (13000 , 12500);

Output

STDNO	SNAME	JOINDATE	FEE	DEPTNO
7369	SMITH	17-DEC-80 12.00.00 AM	13000	1
7521	JONES	02-APR-81 12.00.00 AM	2975	2
7698	BLAKE	01-MAY-81 12.00.00 AM	28500	3

The above output is same for all the Above queries.

Below is the syntax  used to check  multiple expressions  in where condition using IN  operator.

Syntax

WHERE ([EXPRESSION1],[EXPRESSION2]) IN (SELECT COL1, COL2 FROM TBL)

Example query using IN operator with Multiple Expressions.

SELECT * FROM STD WHERE (FEE,DEPTNO) IN (SELECT MAX(FEE),DEPTNO FROM STD GROUP BY DEPTNO);

STDNO	SNAME	JOINDATE	FEE	DEPTNO
7369	SMITH	17-DEC-80 12.00.00 AM	13000	1
7566	WARD	22-FEB-81 12.00.00 AM	12500	3
7654	MARTIN	28-SEP-81 12.00.00 AM	12500	3

The below queries are equivalent to  above query with IN operator.

SELECT * FROM STD WHERE (FEE,DEPTNO) =ANY (SELECT MAX(FEE),DEPTNO FROM STD GROUP BY DEPTNO);

SELECT * FROM STD WHERE (FEE,DEPTNO) =SOME (SELECT MAX(FEE),DEPTNO FROM STD GROUP BY DEPTNO);

NOT IN

please click on the above hyperlink  to know about the usage of  NOT IN operator

SELECT * FROM STD WHERE FEE NOT IN (13000 , 12500);

SELECT * FROM STD WHERE FEE <> all (13000 , 12500);

ANY or SOME

• ANY operator works like an OR operator in SQL.
• SOME operator works similar to ANY operator.
• The ANY comparison condition is used to compare a value to a list or sub query
• It must be preceded by =, !=, >, <, <=, >= and followed by a list or sub query

Syntax

EXPRESSION = ANY(EXPRESSION  1,EXPRESSION 2,......EXPRESSION N)

equivalent to

EXPRESSION = EXPRESSION  1 OR  EXPRESSION = EXPRESSION  2 .....EXPRESSION = EXPRESSION  N

For example, if you have EXPR=ANY(10,20) in where condition of select query,
Then the equivalent of OR in where condition is EXPR=10 OR EXPR=20.

Query Examples to show usage of  ANY and some Operators

Example query using =ANY and = SOME operator

SELECT * FROM STD WHERE FEE=ANY (13000 , 12500);

SELECT * FROM STD WHERE FEE = some (13000 , 12500);

the above queries can be written using OR operator with select query as follows

SELECT * FROM STD WHERE FEE=13000 OR FEE=12500;

Example query using > ANY or > SOME operator

SELECT * FROM STD WHERE FEE >ANY (13000 , 12500);SELECT * FROM STD WHERE FEE > some (13000 , 12500);

the above query can be written using OR operator with select query as follows

SELECT * FROM STD WHERE FEE >13000 OR FEE > 12500;

Example query using < ANY OR < SOME operator

SELECT * FROM STD WHERE FEE <ANY (13000 , 12500);

SELECT * FROM STD WHERE FEE < some (13000 , 12500);

the above query can be written using OR operator with select query as follows

SELECT * FROM STD WHERE FEE <13000 OR FEE < 12500;

Notes:

Assuming subqueries don’t return zero rows, the following statements can be made for both list and subquery versions:

• “expression = ANY (…)”: The value must match one or more values in the list to evaluate to TRUE.
• “expression != ANY (…)”: The value must not match one or more values in the list to evaluate to TRUE.
• “expression > ANY (…)”: The value must be greater than the smallest value in the list to evaluate to TRUE.
• “expression < ANY (…)”: The value must be smaller than the biggest value in the list to evaluate to TRUE.
• “expression >= ANY (…)”: The value must be greater than or equal to the smallest value in the list to evaluate to TRUE.
• “expression <= ANY (…)”: The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE.

ALL

• ALL operator is similar to AND operator in SQL.
• ALL comparison condition is used to compare a value to a list or sub query.
•  It must be preceded by =, !=, >, <, <=, >= and followed by a list or sub query

Syntax:

EXPRESSION = ALL(EXPRESSION  1,EXPRESSION 2,......EXPRESSION N)

equivalent to

EXPRESSION = EXPRESSION  1 AND EXPRESSION = EXPRESSION  2 AND .....EXPRESSION = EXPRESSION  N

Query examples using ALL operator

Example query using > ALL

SELECT * FROM STD WHERE FEE >ALL (13000 , 12500);

SELECT * FROM STD WHERE FEE >13000 AND FEE > 12500;

Example query using = ALL

SELECT * FROM STD WHERE FEE =ALL (13000 , 12500);

SELECT * FROM STD WHERE FEE =13000 AND FEE = 12500;

Example query using < ALL

SELECT * FROM STD WHERE FEE <ALL (13000 , 12500);

SELECT * FROM STD WHERE FEE <13000 AND FEE < 12500;

Assuming sub queries don’t return zero rows, the following statements can be made for both list and sub query versions

• “EXPRESSION = ALL (…)”: The value must match all the values in the list to evaluate to TRUE.
• “EXPRESSION != ALL (…)”: The value must not match any values in the list to evaluate to TRUE.
• “EXPRESSION > ALL (…)”: The value must be greater than the biggest value in the list to evaluate to TRUE.
• “EXPRESSION < ALL (…)”: The value must be smaller than the smallest value in the list to evaluate to TRUE.
• “EXPRESSION >= ALL (…)”: The value must be greater than or equal to the biggest value in the list to evaluate to TRUE.
• “EXPRESSION <= ALL (…)”: The value must be smaller than or equal to the smallest value in the list to evaluate to TRUE.

Notes to Remember :

• IN operator is equivalent to =ANY operator.
• NOT IN operator is equivalent to <> ALL operator.