SUPERSET Operator

Description

The SUPERSET operator returns TRUE(1) when a second operand is a proper subset of a first operand; that is, the first one is larger than the second one. If two operands are identical, FALSE(0) is returned. Note that SUPERSET is not supported if all operands are LIST type.

Syntax

collection_operand SUPERSET collection_operand

Example

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

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

           id  name                  address               zip_code

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

            4  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            5  'Kim       '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            6  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 5}

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

 

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

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

 

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

 

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

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

           id  name                  address               zip_code

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

            3  'Jones     '          {'city', 'country', 'state'}  {1, 2, 3, 4} 

            4  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            5  'Kim       '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            6  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 5}