tQL - Functions as IN / NOT IN operands
A tQL function can be used directly as the right-hand operand of in and not in. The engine either
builds a terms filter from the values the function returns, or lets the function contribute its own
query - so a function can restrict a listing without the caller spelling the values out.
select id, name from customer where id in someIdFunction()
select key, customerId from ticket where customerId not in someIdFunction()
What a function may return
| Return value | How it is used |
|---|---|
| a collection of values | the values become the terms of the in filter; null entries are ignored |
| a ready-made query | used as is, for a function that expresses the condition itself (e.g. a rewrite onto another field) |
Mixing literals and functions
Literal values and function operands can appear in the same list. The result is their union:
select id from customer where id in ('CID-1', 'CID-2', someIdFunction())
Empty right-hand side
An empty right-hand side is a defined state, never "no filter":
| Expression | Function returns nothing |
|---|---|
x in fn() | selects no rows |
x not in fn() | selects all rows |
not in wraps the combined condition in a must_not - it is the supported way to negate a
set-returning function. Do not negate one with != / <>; only in and not in are supported for
a function operand.
Which functions are available as in operands depends on the deployment; some are contributed by
add-on modules rather than the core engine.