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

STDNOSNAMEJOINDATEFEEDEPTNO
7369SMITH17-DEC-80 12.00.00 AM130001
7499ALLEN20-FEB-81 12.00.00 AM160003
7521WARD22-FEB-81 12.00.00 AM125003
7566JONES02-APR-81 12.00.00 AM29752
7654MARTIN28-SEP-81 12.00.00 AM125003
7698BLAKE01-MAY-81 12.00.00 AM285003
7844TURNER08-SEP-81 12.00.00 AM15003

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

STDNOSNAMEJOINDATEFEEDEPTNO
7369SMITH17-DEC-80 12.00.00 AM130001
7521JONES02-APR-81 12.00.00 AM29752
7698BLAKE01-MAY-81 12.00.00 AM285003

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);
STDNOSNAMEJOINDATEFEEDEPTNO
7369SMITH17-DEC-80 12.00.00 AM130001
7566WARD22-FEB-81 12.00.00 AM125003
7654MARTIN28-SEP-81 12.00.00 AM125003

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.

Related Posts