SUBSET Operator

Description

The SUBSET operator returns TRUE(1) if the second operand contains all elements of the first operand. If the first and the second collection have the same elements, FALSE(0) is returned. Note that both operands are the LIST type, the SUBSET operation is not supported.

Syntax

collection_operand SUBSET collection_operand

Example

--selecting rows when the first operand is a subset of the second operand and they are not same

SELECT id, name, address, zip_code FROM contain_tbl WHERE address SUBSET {'country','state','city'};

           id  name                  address               zip_code

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

            1  'Kim       '          {'country', 'state'}  {1, 2, 3}

            2  'Moy       '          {'country', 'state'}  {3, 2, 1}

 

--SUBSET operator cannot be used for comparison between LIST and LIST type values

SELECT id, name, address, zip_code FROM contain_tbl WHERE zip_code SUBSET {1,2,3};

 

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

 

--Comparing operands with a SUBSET operator after casting LIST type as SET type

SELECT id, name, address, zip_code FROM contain_tbl WHERE zip_code SUBSET (CAST ({1,2,3} AS SET));

           id  name                  address               zip_code

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

            7  'Brown     '          {'city', 'country', 'state', 'street'}  {}