SETEXCLUDES() function

SETEXCLUDES() returns True when a specified value is not found within an IDSET or STRINGSET column.

Syntax

SETEXCLUDES(
  {idset-column, integer-value} |
  {stringset-column, 'string-value'}
  )

Arguments

Argument	Description	Required
`idset-column`	Column of `idset` data type to search within	Yes
`integer-value`	Single integer value to search for within `idset-column`	Yes
`stringset-column`	Column of `stringset` data type to search within	Yes
`string-value`	Single string value contained in single quotes to search for within `stringset-column`	Yes

Additional information

Use a WHERE clause with AND operator to add two or more SETINCLUDES() functions.

Returns

Column contains	Data type	Result
No specified value	Boolean	True

Examples

Segments table

create table segments
    (_id id, segment stringset);
insert into segments(_id, segment)
    values (1, ['RED', 'BLUE', 'GREEN']);

+-----+------------------+
| _id | segment          |
+-----+------------------+
|   1 | [RED BLUE GREEN] |
+-----+------------------+

Testing set membership in the select list

This query returns true.

select setexcludes(segment, 'purple') as NOTPURPLE
    from segments;  

Testing set membership as a where clause filter

This query returns true with the selected _id.

select _id from segments where setexcludes(segment, 'purple');

Testing set membership as a where clause filter with multiple set members

This query returns true with the selected _id.

select _id from segments where setexcludes(segment, 'purple') and setexcludes(segment, 'yellow');