ANY/SOME/ALL Conditional Expressions

Description

Group conditional expressions that include quantifiers such as ANY/SOME/aLL perform comparison operation on one data value and on some or all values included in the list. A conditional expression that includes ANY or SOME returns TRUE if the value of the data on the left satisfies simple comparison with at least one of the values in the list specified as an operand on the right. A group conditional expression that includes ALL returns TRUE if the value of the data on the left satisfies simple comparison with all values in the list on the right.

When a comparison operation is performed on NULL in a group conditional expression that includes ANY or SOME, UNKNOWN or TRUE is returned as the result; when a comparison operation is performed on NULL in a group conditional expression that includes ALL, UNKNOWN or FALSE is returned.

Syntax

expression comp_op SOME expression

expression comp_op ANY expression

expression comp_op ALL expression

Example

--creating a table

 

CREATE TABLE condition_tbl (id int primary key, name char(10), dept_name VARCHAR, salary INT);

INSERT INTO condition_tbl VALUES(1, 'Kim', 'devel', 4000000);

INSERT INTO condition_tbl VALUES(2, 'Moy', 'sales', 3000000);

INSERT INTO condition_tbl VALUES(3, 'Jones', 'sales', 5400000);

INSERT INTO condition_tbl VALUES(4, 'Smith', 'devel', 5500000);

INSERT INTO condition_tbl VALUES(5, 'Kim', 'account', 3800000);

INSERT INTO condition_tbl VALUES(6, 'Smith', 'devel', 2400000);

INSERT INTO condition_tbl VALUES(7, 'Brown', 'account', NULL);

 

--selecting rows where department is sales or devel

SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};

           id  name                  dept_name                  salary

======================================================================

            1  'Kim       '          'devel'                   4000000

            2  'Moy       '          'sales'                   3000000

            3  'Jones     '          'sales'                   5400000

            4  'Smith     '          'devel'                   5500000

            6  'Smith     '          'devel'                   2400000

 

--selecting rows comparing NULL value in the ALL group conditions

SELECT * FROM condition_tbl WHERE salary > ALL{3000000, 4000000, NULL};

There are no results.

 

--selecting rows comparing NULL value in the ANY group conditions

SELECT * FROM condition_tbl WHERE salary > ANY{3000000, 4000000, NULL};

           id  name                  dept_name                  salary

======================================================================

            1  'Kim       '          'devel'                   4000000

            3  'Jones     '          'sales'                   5400000

            4  'Smith     '          'devel'                   5500000

            5  'Kim       '          'account'                 3800000

 

--selecting rows where salary*0.9 is less than those salary in devel department

SELECT * FROM condition_tbl WHERE (

(0.9 * salary) < ALL (SELECT salary FROM condition_tbl

WHERE dept_name = 'devel')

);

           id  name                  dept_name                  salary

======================================================================

            6  'Smith     '          'devel'                   2400000