Filter out listed values
Exclude rows with known identifiers, statuses, or other values held in an application array.
Drizzle ORM · Function
notInArray
notInArray filters rows whose column value is not among a supplied array or the results of a subquery. It produces a SQL NOT IN condition.
How it works
Use notInArray to create a filter that keeps rows whose column value is absent from an array or from the results of a subquery. Drizzle translates it to SQL using the NOT IN operator.
Syntax
notInArray(column, values)Parameters
| Name | Type | Description |
|---|---|---|
column | Column | The table column whose value is tested. |
values | array | SQL query | An array of comparison values or a query selecting comparison values. |
Returns
A SQL condition expression for use in a query filter.
Key points
- notInArray creates a predicate that accepts rows when the column value is absent from the provided values.
- The function is imported directly from the drizzle-orm package.
- Place the condition inside where() and provide the column as the first argument.
- An array of values is translated into a SQL NOT IN list.
- A select query can provide the comparison values when filtering against another table.
- With a subquery, Drizzle generates NOT IN followed by the selected query.
Example: Exclude matching values
import { notInArray } from "drizzle-orm";
const result = await db
.select()
.from(users)
.where(notInArray(users.id, [1, 2, 3]));Selects users whose id is not among the specified values.
When to use it
Filter against a subquery
Exclude rows whose column matches values selected from another table.
Interview questions
QWhat does notInArray do?
notInArray builds a condition stating that a column does not match any value in the supplied array. For example, it generates a SQL NOT IN predicate.
Follow-up: How can the comparison set come from the database? Pass a select query instead of a literal array; Drizzle emits NOT IN with a subquery.
QHow do you use notInArray in a query?
Import notInArray from drizzle-orm and pass the table column followed by an array of values. Use the resulting expression in where() to exclude those values.
Follow-up: What SQL shape does the array form produce? It becomes a NOT IN list containing the supplied values.
QCan notInArray use a subquery?
The second argument can be a query selecting one column, not only an array. This lets the filter exclude rows whose value appears in another query's result.
Follow-up: What is the generated SQL shape for that form? The predicate uses NOT IN followed by a scalar subquery.
Related
inArrayeqnotandor