> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Comparison Operators

> Filter by equality, ranges, string matching, and lists with UQL comparison operators.

Source: https://uql-orm.dev/querying/comparison-operators

Each operator is typed by the field it applies to, and only the operators that fit a field’s type are accepted:

- **Every field**: `$eq`, `$ne`, `$not`, `$in`, `$nin`, `$isNull`, `$isNotNull` (a bare array is an implicit `$in` on scalar fields).
- **Comparable fields** (`string`, `number`, `bigint`, `Date`): `$lt`, `$lte`, `$gt`, `$gte`, `$between`.
- **String fields**: `$like`, `$ilike`, `$regex`, `$startsWith`, `$istartsWith`, `$endsWith`, `$iendsWith`, `$includes`, `$iincludes`.
- **Array fields**: `$all`, `$size`, `$elemMatch`.

An inapplicable combination, such as `{ age: { $like: '3%' } }`, `{ active: { $gt: false } }` or `{ name: { $size: 3 } }`, is a compile error.

| Name | Description |
| - | - |
| `$eq` | Equal to. |
| `$ne` | Not equal to. On SQL a `NULL` value is neither equal nor not, so its row is left out; see [`NULL`](#null-is-compared-as-the-engine-compares-it). |
| `$lt` | Less than. |
| `$lte` | Less than or equal to. |
| `$gt` | Greater than. |
| `$gte` | Greater than or equal to. |
| `$like` | `LIKE` pattern over the whole string (case sensitive), read the same on every engine: `%` is any run of characters, `_` any one, and `\` makes the character after it literal (`'50\%'` matches `50%`). E.g. `{ name: { $like: 'John%' } }`. A pattern whose last `\` has nothing after it to escape, such as `'John\'`, is refused. |
| `$ilike` | A `$like` pattern, case insensitive. E.g. `{ name: { $ilike: 'john%' } }`. |
| `$regex` | Regular expression match, case-sensitive on every engine. E.g. `{ name: { $regex: '^test' } }`. On MSSQL it needs a 2025 server at compatibility level 170, and `bun:sqlite` has no `REGEXP` to run it. |
| `$startsWith` | Starts with the text, taken literally (case-sensitive): a `%` or `_` in it is a plain character. |
| `$istartsWith` | Starts with the text, taken literally (case-insensitive). |
| `$endsWith` | Ends with the text, taken literally (case-sensitive). |
| `$iendsWith` | Ends with the text, taken literally (case-insensitive). |
| `$includes` | Contains the text, taken literally (case-sensitive). |
| `$iincludes` | Contains the text, taken literally (case-insensitive). |
| `$in` | Value matches any in a given array. |
| `$nin` | Value matches none in a given array. |
| `$between` | Value is between two bounds (inclusive). E.g. `{ age: { $between: [18, 65] } }`. |
| `$isNull` | Field is null. E.g. `{ deletedAt: { $isNull: true } }`. |
| `$isNotNull` | Field is not null. E.g. `{ email: { $isNotNull: true } }`. |
| `$all` | Array contains all specified values: a scalar compared by JSON type, so `'5'` is not `5`, and an object or an array matched by what it holds. E.g. `{ tags: { $all: ['ts', 'orm'] } }`. |
| `$size` | Array has the specified length. Accepts a number for exact match (`{ tags: { $size: 3 } }`) or comparison operators (`{ tags: { $size: { $gte: 2 } } }`). Also filters by a [relation’s size](https://uql-orm.dev/querying/relations.md#relation-count-filtering-size-subqueries); to *return* or rank by that size instead, see [counting](https://uql-orm.dev/querying/counting.md). |
| `$elemMatch` | Array contains an element matching the condition. Object elements take a value or an operator map per key (`{ addresses: { $elemMatch: { city: 'NYC', zip: { $startsWith: '10' } } } }`), a value matched by what it holds as in `$all`; scalar elements take an operator map (`{ tags: { $elemMatch: { $startsWith: 'ad' } } }`), where one `$eq` or `$in` compares by JSON type as `$all` does. |
| `$text` | Full-text search. See [Full-Text Search](https://uql-orm.dev/querying/full-text.md) for per-dialect SQL and index requirements. |

## Practical Example

```ts title="You write"
import { pool } from './uql.config.js';

import { User } from './shared/models/index.js';

const users = await pool.findMany(User, {
  $select: { id: true, name: true },
  $where: {
    name: { $istartsWith: 'Some', $ne: 'Something' },
    age: { $gte: 18, $lte: 65 },
  },
  $sort: { name: 'asc' },
  $limit: 50,
});
```

## Context-Aware SQL Generation

The case-insensitive operators match the same rows on every engine, each in its own way: PostgreSQL and CockroachDB have `ILIKE`; SQLite’s `LIKE` already ignores case on both sides; the MySQL family has neither, so both sides are lowered explicitly, which is why you see `LOWER(...)` there and nowhere else.

PostgreSQL:

```sql
SELECT "id", "name" FROM "User"
WHERE ("name" ILIKE $1 ESCAPE '\' AND "name" <> $2) AND ("age" >= $3 AND "age" <= $4)
ORDER BY "name"
LIMIT 50
-- values: ['Some%', 'Something', 18, 65]
```

MySQL / MariaDB:

```sql
SELECT `id`, `name` FROM `User`
WHERE (LOWER(`name`) LIKE ? ESCAPE '\\' AND `name` <> ?) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50
-- values: ['some%', 'Something', 18, 65]
```

SQLite:

```sql
SELECT `id`, `name` FROM `User`
WHERE (`name` LIKE ? ESCAPE '\' AND `name` <> ?) AND (`age` >= ? AND `age` <= ?)
ORDER BY `name`
LIMIT 50
-- values: ['Some%', 'Something', 18, 65]
```

> **What case-insensitive costs, per engine**
>
> On the MySQL family only an [expression index](https://uql-orm.dev/entities/indexes.md) over `LOWER(column)` can serve these. That costs an index for `$istartsWith` alone: the other patterns lead with `%`, which no plain index could serve anyway. Their default collation is case-insensitive, so `$startsWith` matches either case there and keeps its index, at the price of matching case-sensitively on PostgreSQL. On SQLite the folding is ASCII-only whichever way you write it, since the engine has no case mapping for accented characters without ICU.

## `NULL` is compared as the engine compares it

Each engine keeps its own rules. SQL’s three-valued logic makes `NULL <> 'a'` neither true nor false, so every negation leaves a `NULL` row out; MongoDB compares a missing or `null` field as a value, so it keeps one:

| You write | SQL, `code` is `NULL` | MongoDB, `code` is missing or `null` |
| - | - | - |
| `{ code: { $ne: 'a' } }` | left out | matches |
| `{ code: { $nin: ['a'] } }` | left out | matches |
| `{ code: { $in: ['a', null] } }` | left out | matches |
| `{ code: { $not: { $eq: 'a' } } }`, `{ $nor: [{ code: 'a' }] }` | left out | matches |

Name `NULL` where you want the same rows everywhere: `{ $or: [{ code: { $ne: 'a' } }, { code: null }] }`. `{ code: null }` and `{ code: { $ne: null } }` ask for a `NULL` or its absence on every engine.

## `$between`: Range Queries

```ts title="You write"
const users = await pool.findMany(User, {
  $where: { age: { $between: [18, 65] } },
});
```

PostgreSQL:

```sql
SELECT * FROM "User" WHERE "age" BETWEEN $1 AND $2
```

## JSONB Dot-Notation Operators

The operators above also work on nested JSON paths with **dot-notation** (e.g. `'settings.isArchived': { $ne: true }`) on PostgreSQL, MySQL, MariaDB and SQLite. [JSON / JSONB](https://uql-orm.dev/querying/json.md) covers filtering, the `$set`/`$unset`/`$push`/`$pull` update operators, and sorting by JSON paths.

---

## Next Steps

- [Logical Operators](https://uql-orm.dev/querying/logical-operators.md): Compose conditions with `$and`, `$or`, `$not`, `$nor`.
- [Deep Relations](https://uql-orm.dev/querying/relations.md): Filter and count across related entities.
- [Counting](https://uql-orm.dev/querying/counting.md): Count matches, test existence, and tally relations.
- [JSON / JSONB](https://uql-orm.dev/querying/json.md): The same operators on nested JSON paths.
- [Querier API](https://uql-orm.dev/querying/querier.md): The full query API these operators live in.