Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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

  • comp_op : A comparison operator >, = or <= can be used.
  • expression (left) : A single-value column, path expression, constant value or arithmetic function that produces a single value can be used.
  • expression (right) : A column name, path expression, list (set) of constant values or subquery can be used. A list is a set represented within braces ({}). If a subquery is used, expression (left) and comparison operation on all results of the subquery execution is performed.
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