Containment Operators

Description

Containment operators are used to check the containment relationship by performing comparison operation on operands of the collection data type. Collection 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

• collection_operand: This expression that can be specified as an operand is a single SET-valued attribute, an arithmetic expression containing a SET operator or a SET value enclosed in braces. If the type is not specified, the SET value enclosed in braces is treated as a LIST type by default.
• Subqueries can be specified as operands. If a column which is not a collection type is searched, a collection data type keyword is required for the subquery like SET(subquery)). The column retrieved by a subquery must return a single set so that it can be compared with the set of the other operands.
• If the element type of collection is an object, the OIDs, not its contents, are compared. For example, two objects with different OIDs are considered to be different even though they have the same attribute values.
  • NULL: Any of operands to be compared is NULL, NULL is returned.

Example

--empty set is a subset of any set

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

       Result

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

            1

 

--operation between set type and null returns null

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

       Result

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

        NULL

 

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

SELECT ((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

SELECT ((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

SELECT ((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

SELECT ((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

SELECT ((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

SELECT ((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

SELECT ((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

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

       Result

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

            1

--error occurs because LIST subseteq LIST is not supported

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

 

ERROR: ' subseteq ' operator is not defined on types sequence and sequence.