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.
collection_operand SUBSET collection_operand
--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'} {}