import IsSupportedChipGroup from '@mdx/IsSupportedChipGroup.astro'; import Section from '@mdx/Section.astro'; # Filter and conditional operators We natively support all dialect specific filter and conditional operators. You can import all filter & conditional from `drizzle-orm`: ```typescript copy import { eq, ne, gt, gte, ... } from "drizzle-orm"; ``` ### eq Value equal to `n`

```typescript copy import { eq } from "drizzle-orm"; db.select().from(table).where(eq(table.column, 5)); ``` ```sql copy SELECT * FROM table WHERE table.column = 5 ```

```typescript import { eq } from "drizzle-orm"; db.select().from(table).where(eq(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 = table.column2 ```

### ne Value is not equal to `n`

```typescript import { ne } from "drizzle-orm"; db.select().from(table).where(ne(table.column, 5)); ``` ```sql SELECT * FROM table WHERE table.column <> 5 ```

```typescript import { ne } from "drizzle-orm"; db.select().from(table).where(ne(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 <> table.column2 ```

## --- ### gt Value is greater than `n`

```typescript import { gt } from "drizzle-orm"; db.select().from(table).where(gt(table.column, 5)); ``` ```sql SELECT * FROM table WHERE table.column > 5 ```

```typescript import { gt } from "drizzle-orm"; db.select().from(table).where(gt(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 > table.column2 ```

### gte Value is greater than or equal to `n`

```typescript import { gte } from "drizzle-orm"; db.select().from(table).where(gte(table.column, 5)); ``` ```sql SELECT * FROM table WHERE table.column >= 5 ```

```typescript import { gte } from "drizzle-orm"; db.select().from(table).where(gte(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 >= table.column2 ```

### lt Value is less than `n`

```typescript import { lt } from "drizzle-orm"; db.select().from(table).where(lt(table.column, 5)); ``` ```sql SELECT * FROM table WHERE table.column < 5 ```

```typescript import { lt } from "drizzle-orm"; db.select().from(table).where(lt(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 < table.column2 ```

### lte Value is less than or equal to `n`.

```typescript import { lte } from "drizzle-orm"; db.select().from(table).where(lte(table.column, 5)); ``` ```sql SELECT * FROM table WHERE table.column <= 5 ```

```typescript import { lte } from "drizzle-orm"; db.select().from(table).where(lte(table.column1, table.column2)); ``` ```sql SELECT * FROM table WHERE table.column1 <= table.column2 ```

## --- ### exists Value exists

```typescript import { exists } from "drizzle-orm"; const query = db.select().from(table2) db.select().from(table).where(exists(query)); ``` ```sql SELECT * FROM table WHERE EXISTS (SELECT * from table2) ```

### notExists

```typescript import { notExists } from "drizzle-orm"; const query = db.select().from(table2) db.select().from(table).where(notExists(query)); ``` ```sql SELECT * FROM table WHERE NOT EXISTS (SELECT * from table2) ```

### isNull Value is `null`

```typescript import { isNull } from "drizzle-orm"; db.select().from(table).where(isNull(table.column)); ``` ```sql SELECT * FROM table WHERE table.column IS NULL ```

### isNotNull Value is not `null`

```typescript import { isNotNull } from "drizzle-orm"; db.select().from(table).where(isNotNull(table.column)); ``` ```sql SELECT * FROM table WHERE table.column IS NOT NULL ```

## --- ### inArray Value is in array of values

```typescript import { inArray } from "drizzle-orm"; db.select().from(table).where(inArray(table.column, [1, 2, 3, 4])); ``` ```sql SELECT * FROM table WHERE table.column in (1, 2, 3, 4) ```

```typescript import { inArray } from "drizzle-orm"; const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(inArray(table.column, query)); ``` ```sql SELECT * FROM table WHERE table.column IN (SELECT table2.column FROM table2) ```

### notInArray Value is not in array of values

```typescript import { notInArray } from "drizzle-orm"; db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4])); ``` ```sql SELECT * FROM table WHERE table.column NOT in (1, 2, 3, 4) ```

```typescript import { notInArray } from "drizzle-orm"; const query = db.select({ data: table2.column }).from(table2); db.select().from(table).where(notInArray(table.column, query)); ``` ```sql SELECT * FROM table WHERE table.column NOT IN (SELECT table2.column FROM table2) ```

## --- ### between Value is between two values

```typescript import { between } from "drizzle-orm"; db.select().from(table).where(between(table.column, 2, 7)); ``` ```sql SELECT * FROM table WHERE table.column BETWEEN 2 AND 7 ```

### notBetween Value is not between two value

```typescript import { notBetween } from "drizzle-orm"; db.select().from(table).where(notBetween(table.column, 2, 7)); ``` ```sql SELECT * FROM table WHERE table.column NOT BETWEEN 2 AND 7 ```

## --- ### like Value is like other value, case sensitive

```typescript import { like } from "drizzle-orm"; db.select().from(table).where(like(table.column, "%llo wor%")); ``` ```sql SELECT * FROM table WHERE table.column LIKE '%llo wor%' ```

### ilike Value is like some other value, case insensitive

```typescript import { ilike } from "drizzle-orm"; db.select().from(table).where(ilike(table.column, "%llo wor%")); ``` ```sql SELECT * FROM table WHERE table.column ILIKE '%llo wor%' ```

### notIlike Value is not like some other value, case insensitive

```typescript import { notIlike } from "drizzle-orm"; db.select().from(table).where(notIlike(table.column, "%llo wor%")); ``` ```sql SELECT * FROM table WHERE table.column NOT ILIKE '%llo wor%' ```

## --- ### not All conditions must return `false`.

```typescript import { eq, not } from "drizzle-orm"; db.select().from(table).where(not(eq(table.column, 5))); ``` ```sql SELECT * FROM table WHERE NOT (table.column = 5) ```

### and All conditions must return `true`.

```typescript import { gt, lt, and } from "drizzle-orm"; db.select().from(table).where(and(gt(table.column, 5), lt(table.column, 7))); ``` ```sql SELECT * FROM table WHERE (table.column > 5 AND table.column < 7) ```

### or One or more conditions must return `true`.

```typescript import { gt, lt, or } from "drizzle-orm"; db.select().from(table).where(or(gt(table.column, 5), lt(table.column, 7))); ``` ```sql SELECT * FROM table WHERE (table.column > 5 OR table.column < 7) ```

## --- ### arrayContains Test that a column or expression contains all elements of the list passed as the second argument

```typescript import { arrayContains } from "drizzle-orm"; const contains = await db.select({ id: posts.id }).from(posts) .where(arrayContains(posts.tags, ['Typescript', 'ORM'])); const withSubQuery = await db.select({ id: posts.id }).from(posts) .where(arrayContains( posts.tags, db.select({ tags: posts.tags }).from(posts).where(eq(posts.id, 1)), )); ``` ```sql select "id" from "posts" where "posts"."tags" @> {Typescript,ORM}; select "id" from "posts" where "posts"."tags" @> (select "tags" from "posts" where "posts"."id" = 1); ```

### arrayContained Test that the list passed as the second argument contains all elements of a column or expression

```typescript import { arrayContained } from "drizzle-orm"; const contained = await db.select({ id: posts.id }).from(posts) .where(arrayContained(posts.tags, ['Typescript', 'ORM'])); ``` ```sql select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM}; ```

### arrayOverlaps Test that a column or expression contains any elements of the list passed as the second argument.

```typescript import { arrayOverlaps } from "drizzle-orm"; const overlaps = await db.select({ id: posts.id }).from(posts) .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM'])); ``` ```sql select "id" from "posts" where "posts"."tags" && {Typescript,ORM} ```

filter-and-conditional-operators