필터 및 조건 연산자

모든 데이터베이스별 필터 및 조건 연산자를 기본적으로 지원합니다.

모든 필터 및 조건 연산자는 drizzle-orm에서 import할 수 있습니다:

import { eq, ne, gt, gte, ... } from "drizzle-orm";

eq

PostgreSQL

MySQL

SQLite

SingleStore

값이 n과 같음

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column, 5));

SELECT * FROM table WHERE table.column = 5

import { eq } from "drizzle-orm";

db.select().from(table).where(eq(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 = table.column2

ne

PostgreSQL

MySQL

SQLite

SingleStore

값이 n과 같지 않음

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column, 5));

SELECT * FROM table WHERE table.column <> 5

import { ne } from "drizzle-orm";

db.select().from(table).where(ne(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 <> table.column2

---

gt

PostgreSQL

MySQL

SQLite

SingleStore

값이 n보다 큼

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column, 5));

SELECT * FROM table WHERE table.column > 5

import { gt } from "drizzle-orm";

db.select().from(table).where(gt(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 > table.column2

gte

PostgreSQL

MySQL

SQLite

SingleStore

값이 n보다 크거나 같음

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column, 5));

SELECT * FROM table WHERE table.column >= 5

import { gte } from "drizzle-orm";

db.select().from(table).where(gte(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 >= table.column2

lt

PostgreSQL

MySQL

SQLite

SingleStore

값이 n보다 작음

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column, 5));

SELECT * FROM table WHERE table.column < 5

import { lt } from "drizzle-orm";

db.select().from(table).where(lt(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 < table.column2

lte

PostgreSQL

MySQL

SQLite

SingleStore

값이 n보다 작거나 같음

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column, 5));

SELECT * FROM table WHERE table.column <= 5

import { lte } from "drizzle-orm";

db.select().from(table).where(lte(table.column1, table.column2));

SELECT * FROM table WHERE table.column1 <= table.column2

---

exists

PostgreSQL

MySQL

SQLite

SingleStore

값이 존재함

import { exists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(exists(query));

SELECT * FROM table WHERE EXISTS (SELECT * from table2)

notExists

import { notExists } from "drizzle-orm";

const query = db.select().from(table2)
db.select().from(table).where(notExists(query));

SELECT * FROM table WHERE NOT EXISTS (SELECT * from table2)

isNull

PostgreSQL

MySQL

SQLite

SingleStore

값이 null임

import { isNull } from "drizzle-orm";

db.select().from(table).where(isNull(table.column));

SELECT * FROM table WHERE table.column IS NULL

isNotNull

PostgreSQL

MySQL

SQLite

SingleStore

값이 null이 아님

import { isNotNull } from "drizzle-orm";

db.select().from(table).where(isNotNull(table.column));

SELECT * FROM table WHERE table.column IS NOT NULL

---

inArray

PostgreSQL

MySQL

SQLite

SingleStore

값이 배열에 포함됨

import { inArray } from "drizzle-orm";

db.select().from(table).where(inArray(table.column, [1, 2, 3, 4]));

SELECT * FROM table WHERE table.column in (1, 2, 3, 4)

import { inArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(inArray(table.column, query));

SELECT * FROM table WHERE table.column IN (SELECT table2.column FROM table2)

notInArray

PostgreSQL

MySQL

SQLite

SingleStore

값이 배열에 포함되지 않음

import { notInArray } from "drizzle-orm";

db.select().from(table).where(notInArray(table.column, [1, 2, 3, 4]));

SELECT * FROM table WHERE table.column NOT in (1, 2, 3, 4)

import { notInArray } from "drizzle-orm";

const query = db.select({ data: table2.column }).from(table2);
db.select().from(table).where(notInArray(table.column, query));

SELECT * FROM table WHERE table.column NOT IN (SELECT table2.column FROM table2)

---

between

PostgreSQL

MySQL

SQLite

SingleStore

값이 두 값 사이에 있음

import { between } from "drizzle-orm";

db.select().from(table).where(between(table.column, 2, 7));

SELECT * FROM table WHERE table.column BETWEEN 2 AND 7

notBetween

PostgreSQL

MySQL

SQLite

SingleStore

값이 두 값 사이에 있지 않음

import { notBetween } from "drizzle-orm";

db.select().from(table).where(notBetween(table.column, 2, 7));

SELECT * FROM table WHERE table.column NOT BETWEEN 2 AND 7

---

like

PostgreSQL

MySQL

SQLite

SingleStore

값이 패턴과 일치함 (대소문자 구분)

import { like } from "drizzle-orm";

db.select().from(table).where(like(table.column, "%llo wor%"));

SELECT * FROM table  WHERE table.column LIKE '%llo wor%'

ilike

PostgreSQL

MySQL

SQLite

SingleStore

값이 패턴과 일치함 (대소문자 구분 안 함)

import { ilike } from "drizzle-orm";

db.select().from(table).where(ilike(table.column, "%llo wor%"));

SELECT * FROM table WHERE table.column ILIKE '%llo wor%'

notIlike

PostgreSQL

MySQL

SQLite

SingleStore

값이 패턴과 일치하지 않음 (대소문자 구분 안 함)

import { notIlike } from "drizzle-orm";

db.select().from(table).where(notIlike(table.column, "%llo wor%"));

SELECT * FROM table WHERE table.column NOT ILIKE '%llo wor%'

---

not

PostgreSQL

MySQL

SQLite

SingleStore

모든 조건이 false를 반환해야 함

import { eq, not } from "drizzle-orm";

db.select().from(table).where(not(eq(table.column, 5)));

SELECT * FROM table WHERE NOT (table.column = 5)

and

PostgreSQL

MySQL

SQLite

SingleStore

모든 조건이 true를 반환해야 함

import { gt, lt, and } from "drizzle-orm";

db.select().from(table).where(and(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM table WHERE (table.column > 5 AND table.column < 7)

or

PostgreSQL

MySQL

SQLite

SingleStore

하나 이상의 조건이 true를 반환해야 함

import { gt, lt, or } from "drizzle-orm";

db.select().from(table).where(or(gt(table.column, 5), lt(table.column, 7)));

SELECT * FROM table WHERE (table.column > 5 OR table.column < 7)

---

arrayContains

PostgreSQL

MySQL

SQLite

SingleStore

컬럼이나 표현식이 두 번째 인자로 전달된 목록의 모든 요소를 포함하는지 검사

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)),
  ));

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

PostgreSQL

MySQL

SQLite

SingleStore

두 번째 인자로 전달된 목록이 컬럼이나 표현식의 모든 요소를 포함하는지 검사

import { arrayContained } from "drizzle-orm";

const contained = await db.select({ id: posts.id }).from(posts)
  .where(arrayContained(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" <@ {Typescript,ORM};

arrayOverlaps

PostgreSQL

MySQL

SQLite

SingleStore

컬럼이나 표현식이 두 번째 인자로 전달된 목록의 요소 중 일부라도 포함하는지 검사

import { arrayOverlaps } from "drizzle-orm";

const overlaps = await db.select({ id: posts.id }).from(posts)
  .where(arrayOverlaps(posts.tags, ['Typescript', 'ORM']));

select "id" from "posts" where "posts"."tags" && {Typescript,ORM}