Containment Operators

Description

Containment operators are used to check the containment relationship by performing comparison operation on operands of the set data type. Set data types or subqueries can be specified as operands. The operation returns TRUE or FALSE if there is a containment relationship between the two operands of identical/different/subset/proper subset.

The description and return values about the containment operators supported by CUBRID are as follows:

Containment Operators Supported by CUBRID

Containment Operator

Description

Predicates

Return Value

A SETEQ B

A = B
Elements in A and B are same each other.

{1,2} SETEQ {1,2,2}

0

A SETNEQ B

A ≠ B
Elements in A and B are not same each other.

{1,2} SETNEQ {1,2,3}

1

A SUPERSET B

A ⊃ B
B is a proper subset of A.

{1,2} SUPERSET {1,2,3}

0

A SUBSET B

A ⊂ B
A is a proper subset of B.

{1,2} SUBSET {1,2,3}

1

A SUPERSETEQ B

A ⊇ B
B is a subset of A.

{1,2} SUPERSETEQ {1,2,3}

0

A SUBSETEQ B

A ⊆ B
A is a subset of B.

{1,2} SUBSETEQ {1,2,3}

1

The following table shows than possibility of operation by operand and type conversion if a containment operator is used.

Possibility of Operation by Operand

 

SET 

MULTISET

LIST(=SEQUENCE)

SET

Operation possible

Operation possible

Operation possible

MULTISET

Operation possible

Operation possible

Operation possible
(LIST is converted into MULTISET)

LIST(=SEQUENCE)

Operation possible

Operation possible
(LIST is converted into MULTISET)

Some operation possible
(SETEQ, SETNEQ)
Error occurs for the rest of operators.

Syntax

collection_operand  containment_operator  collection_operand

 

collection_operand:

set

multiset

sequence(or list)

subquery

NULL

 

containment_operator:

SETEQ

SETNEQ

SUPERSET

SUBSET

SUPERSETEQ

SUBSETEQ

Example

--empty set is a subset of any set

EVALUATE ({} SUBSETEQ (CAST ({3,1,2} AS SET)));

       Result

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

            1

 

--operation between set type and null returns null

EVALUATE ((CAST ({3,1,2} AS SET)) SUBSETEQ NULL);

       Result

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

        NULL

 

--{1,2,3} seteq {1,2,3} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SETEQ (CAST ({1,2,3,3} AS SET)));

       Result

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

            1

 

--{1,2,3} seteq {1,2,3,3} returns false

EVALUATE ((CAST ({3,1,2} AS SET)) SETEQ (CAST ({1,2,3,3} AS MULTISET)));

       Result

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

            0

 

--{1,2,3} setneq {1,2,3,3} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SETNEQ (CAST ({1,2,3,3} AS MULTISET)));

       Result

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

            1

 

--{1,2,3} subseteq {1,2,3,4} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SUBSETEQ (CAST ({1,2,4,4,3} AS SET)));

       Result

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

            1

 

--{1,2,3} subseteq {1,2,3,4,4} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SUBSETEQ (CAST ({1,2,4,4,3} AS MULTISET)));

       Result

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

            1

 

--{1,2,3} subseteq {1,2,4,4,3} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SUBSETEQ (CAST ({1,2,4,4,3} AS LIST)));

       Result

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

            0

 

--{1,2,3} subseteq {1,2,3,4,4} returns true

EVALUATE ((CAST ({3,1,2} AS SET)) SUBSETEQ (CAST ({1,2,3,4,4} AS LIST)));

       Result

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

            1

 

--{3,1,2} seteq {3,1,2} returns true

EVALUATE ((CAST ({3,1,2} AS LIST)) SETEQ (CAST ({3,1,2} AS LIST)));

       Result

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

            1

--error occurs because LIST subseteq LIST is not supported

EVALUATE ((CAST ({3,1,2} AS LIST)) SUBSETEQ (CAST ({3,1,2} AS LIST)));

       Result

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

        error