집합 연산

SQL 집합 연산은 여러 쿼리 블록의 결과를 하나로 합칩니다. SQL 표준은 다음과 같은 세 가지 집합 연산을 정의합니다: UNION, INTERSECT, EXCEPT, UNION ALL, INTERSECT ALL, EXCEPT ALL.

Union

두 쿼리 블록의 모든 결과를 하나로 합치고, 중복된 값을 제거합니다.

customers와 users 테이블에서 중복 없이 모든 이름을 가져옵니다.

PostgreSQL

MySQL

SQLite

SingleStore

import-pattern

builder-pattern

schema.ts

import { union } from 'drizzle-orm/pg-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
    allNamesForUserQuery,
    db.select({ name: customers.name }).from(customers)
).limit(10);

(select "name" from "sellers")
union
(select "name" from "customers")
limit $1

import { users, customers } from './schema'

const result = await db
  .select({ name: users.name })
  .from(users)
  .union(db.select({ name: customers.name }).from(customers))
  .limit(10);

(select "name" from "sellers")
union
(select "name" from "customers")
limit $1

import { integer, pgTable, text, varchar } from "drizzle-orm/pg-core";

const users = pgTable('sellers', {
    id: integer('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    address: text('address'),
});

const customers = pgTable('customers', {
    id: integer('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    city: text('city'),
    email: varchar('email', { length: 256 }).notNull()
});

import-pattern

builder-pattern

schema.ts

import { union } from 'drizzle-orm/mysql-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
    allNamesForUserQuery,
    db.select({ name: customers.name }).from(customers)
).limit(10);

(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?

import { users, customers } from './schema'

const result = await db
  .select({ name: users.name })
  .from(users)
  .union(db.select({ name: customers.name }).from(customers))
  .limit(10);

(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?

import { int, mysqlTable, text, varchar } from "drizzle-orm/mysql-core";

const users = mysqlTable('sellers', {
    id: int('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    address: text('address'),
});

const customers = mysqlTable('customers', {
    id: int('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    city: text('city'),
    email: varchar('email', { length: 256 }).notNull()
});

import-pattern

builder-pattern

schema.ts

import { union } from 'drizzle-orm/sqlite-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
    allNamesForUserQuery,
    db.select({ name: customers.name }).from(customers)
).limit(10);

(select "name" from "sellers")
union 
(select "name" from "customers")
limit ?

import { users, customers } from './schema'

const result = await db
  .select({ name: users.name })
  .from(users)
  .union(db.select({ name: customers.name }).from(customers))
  .limit(10);

select "name" from "sellers" union select "name" from "customers" limit ?

import { int, sqliteTable, text } from "drizzle-orm/sqlite-core";

const users = sqliteTable('sellers', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
    address: text('address'),
});

const customers = sqliteTable('customers', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
    city: text('city'),
    email: text('email').notNull()
});

import-pattern

builder-pattern

schema.ts

import { union } from 'drizzle-orm/singlestore-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
    allNamesForUserQuery,
    db.select({ name: customers.name }).from(customers)
).limit(10);

(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?

import { users, customers } from './schema'

const result = await db
  .select({ name: users.name })
  .from(users)
  .union(db.select({ name: customers.name }).from(customers))
  .limit(10);

(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?

import { int, mysqlTable, text, varchar } from "drizzle-orm/singlestore-core";

const users = mysqlTable('sellers', {
    id: int('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    address: text('address'),
});

const customers = mysqlTable('customers', {
    id: int('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    city: text('city'),
    email: varchar('email', { length: 256 }).notNull()
});

UNION ALL

두 쿼리 블록의 결과를 중복을 포함하여 하나의 결과로 합칩니다.

온라인 판매와 매장 판매를 나타내는 두 개의 테이블이 있다고 가정해 봅시다. 이 경우, 두 테이블의 데이터를 하나의 결과 집합으로 합치고 싶을 수 있습니다. 중복된 거래가 있을 수 있으므로 모든 레코드를 유지하고 중복을 제거하지 않습니다.

PostgreSQL

MySQL

SQLite

SingleStore

import-pattern

builder-pattern

schema.ts

import { unionAll } from 'drizzle-orm/pg-core'
import { onlineSales, inStoreSales } from './schema'

const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);

const result = await unionAll(onlineTransactions, inStoreTransactions);

select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"

import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );

select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"

import { integer, pgTable, text, timestamp, varchar } from "drizzle-orm/pg-core";

const onlineSales = pgTable('online_sales', {
    transactionId: integer('transaction_id').primaryKey(),
    productId: integer('product_id').unique(),
    quantitySold: integer('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

const inStoreSales = pgTable('in_store_sales', {
    transactionId: integer('transaction_id').primaryKey(),
    productId: integer('product_id').unique(),
    quantitySold: integer('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

import-pattern

builder-pattern

schema.ts

import { unionAll } from 'drizzle-orm/mysql-core'
import { onlineSales, inStoreSales } from './schema'

const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);

const result = await unionAll(onlineTransactions, inStoreTransactions);

select `transaction_id` from `online_sales`
union all
select `transaction_id` from `in_store_sales`

import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );

(select `transaction_id` from `online_sales`)
union all 
(select `transaction_id` from `in_store_sales`)

import { int, mysqlTable, text, timestamp, varchar } from "drizzle-orm/mysql-core";

const onlineSales = mysqlTable('online_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

const inStoreSales = mysqlTable('in_store_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

import-pattern

builder-pattern

schema.ts

import { unionAll } from 'drizzle-orm/sqlite-core'
import { onlineSales, inStoreSales } from './schema'

const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);

const result = await unionAll(onlineTransactions, inStoreTransactions);

select "transaction_id" from "online_sales" 
union all 
select "transaction_id" from "in_store_sales"

import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );

select "transaction_id" from "online_sales" 
union all 
select "transaction_id" from "in_store_sales"

import { int, sqliteTable } from "drizzle-orm/sqlite-core";

const onlineSales = sqliteTable('online_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: int('sale_date', { mode: 'timestamp' }),
});

const inStoreSales = sqliteTable('in_store_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: int('sale_date', { mode: 'timestamp' }),
});

IMPORTANT

UNION ALL과 ORDER BY의 동작이 MySQL과 일치하지 않음: SingleStore는 UNION ALL 뒤에 ORDER BY 명령어를 다르게 해석합니다. SingleStore에서는 다음 쿼리가 유효하지만, MySQL에서는 유효하지 않습니다.

import-pattern

builder-pattern

schema.ts

import { unionAll } from 'drizzle-orm/singlestore-core'
import { onlineSales, inStoreSales } from './schema'

const onlineTransactions = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const inStoreTransactions = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);

const result = await unionAll(onlineTransactions, inStoreTransactions);

select `transaction_id` from `online_sales`
union all
select `transaction_id` from `in_store_sales`

import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );

(select `transaction_id` from `online_sales`)
union all 
(select `transaction_id` from `in_store_sales`)

import { int, mysqlTable, text, timestamp, varchar } from "drizzle-orm/singlestore-core";

const onlineSales = mysqlTable('online_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

const inStoreSales = mysqlTable('in_store_sales', {
    transactionId: int('transaction_id').primaryKey(),
    productId: int('product_id').unique(),
    quantitySold: int('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});

교집합(Intersect)

두 쿼리 블록의 결과 중 공통된 행만 결합하고, 중복은 제거합니다.

학생들의 수강 정보를 저장하는 두 테이블이 있다고 가정해 봅시다. 두 개의 다른 학과에서 공통으로 제공하는 강좌를 찾고 싶지만, 동일한 학생이 동일한 강좌를 여러 번 수강한 경우는 제외하고 싶습니다.

이 시나리오에서는 두 학과에서 공통으로 제공하는 강좌를 찾되, 동일한 강좌가 여러 학생에 의해 수강된 경우에도 중복으로 계산하지 않기를 원합니다.

PostgreSQL

MySQL

SQLite

SingleStore

import-pattern

builder-pattern

schema.ts

import { intersect } from 'drizzle-orm/pg-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.courseName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB);

const result = await intersect(departmentACourses, departmentBCourses);

select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"

import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));

select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"

import { integer, pgTable, varchar } from "drizzle-orm/pg-core";

const depA = pgTable('department_a_courses', {
    studentId: integer('student_id'),
    courseName: varchar('course_name').notNull(),
});

const depB = pgTable('department_b_courses', {
    studentId: integer('student_id'),
    courseName: varchar('course_name').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { intersect } from 'drizzle-orm/mysql-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.courseName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB);

const result = await intersect(departmentACourses, departmentBCourses);

select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`

import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));

select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`

import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";

const depA = mysqlTable('department_a_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});

const depB = pgTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});

import-pattern

builder-pattern

schema.ts

import { intersect } from 'drizzle-orm/sqlite-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.courseName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB);

const result = await intersect(departmentACourses, departmentBCourses);

select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"

import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));

select "course_name" from "department_a_courses" 
intersect 
select "course_name" from "department_b_courses"

import { int, sqliteTable, text } from "drizzle-orm/sqlite-core";

const depA = sqliteTable('department_a_courses', {
    studentId: int('student_id'),
    courseName: text('course_name').notNull(),
});

const depB = sqliteTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: text('course_name').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { intersect } from 'drizzle-orm/singlestore-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.courseName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.courseName }).from(depB);

const result = await intersect(departmentACourses, departmentBCourses);

select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`

import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));

select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`

import { int, mysqlTable, varchar } from "drizzle-orm/singlestore-core";

const depA = mysqlTable('department_a_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});

const depB = pgTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});

Intersect All

두 쿼리 블록의 결과에서 공통된 행만 결합하고, 중복된 값을 유지합니다.

예를 들어, 일반 고객과 VIP 고객의 주문 데이터가 담긴 두 테이블이 있다고 가정해봅시다. 이때, 일반 고객과 VIP 고객 모두가 주문한 제품을 식별하고자 합니다. 이 경우, 여러 고객이 동일한 제품을 여러 번 주문하더라도 각 제품의 수량 정보를 유지해야 합니다.

이 시나리오에서는 일반 고객과 VIP 고객 모두가 주문한 제품을 찾되, 여러 고객이 동일한 제품을 여러 번 주문한 경우에도 수량 정보를 유지해야 합니다.

PostgreSQL

MySQL

SingleStore

import-pattern

builder-pattern

schema.ts

import { intersectAll } from 'drizzle-orm/pg-core'
import { regularCustomerOrders, vipCustomerOrders } from './schema'

const regularOrders = db.select({ 
    productId: regularCustomerOrders.productId,
    quantityOrdered: regularCustomerOrders.quantityOrdered }
).from(regularCustomerOrders);

const vipOrders = db.select({ 
    productId: vipCustomerOrders.productId,
    quantityOrdered: vipCustomerOrders.quantityOrdered }
).from(vipCustomerOrders);

const result = await intersectAll(regularOrders, vipOrders);

select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"

import { regularCustomerOrders, vipCustomerOrders } from './schema'

const result = await db
    .select({
      productId: regularCustomerOrders.productId,
      quantityOrdered: regularCustomerOrders.quantityOrdered,
    })
    .from(regularCustomerOrders)
    .intersectAll(
      db
        .select({
          productId: vipCustomerOrders.productId,
          quantityOrdered: vipCustomerOrders.quantityOrdered,
        })
        .from(vipCustomerOrders)
    );

select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"

import { integer, pgTable } from "drizzle-orm/pg-core";

const regularCustomerOrders = pgTable('regular_customer_orders', {
    customerId: integer('customer_id').primaryKey(),
    productId: integer('product_id').notNull(),
    quantityOrdered: integer('quantity_ordered').notNull(),
});

const vipCustomerOrders = pgTable('vip_customer_orders', {
    customerId: integer('customer_id').primaryKey(),
    productId: integer('product_id').notNull(),
    quantityOrdered: integer('quantity_ordered').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { intersectAll } from 'drizzle-orm/mysql-core'
import { regularCustomerOrders, vipCustomerOrders } from './schema'

const regularOrders = db.select({ 
    productId: regularCustomerOrders.productId,
    quantityOrdered: regularCustomerOrders.quantityOrdered }
).from(regularCustomerOrders);

const vipOrders = db.select({ 
    productId: vipCustomerOrders.productId,
    quantityOrdered: vipCustomerOrders.quantityOrdered }
).from(vipCustomerOrders);

const result = await intersectAll(regularOrders, vipOrders);

select `product_id`, `quantity_ordered` from `regular_customer_orders`
intersect all
select `product_id`, `quantity_ordered` from `vip_customer_orders`

import { regularCustomerOrders, vipCustomerOrders } from './schema'

const result = await db
    .select({
      productId: regularCustomerOrders.productId,
      quantityOrdered: regularCustomerOrders.quantityOrdered,
    })
    .from(regularCustomerOrders)
    .intersectAll(
      db
        .select({
          productId: vipCustomerOrders.productId,
          quantityOrdered: vipCustomerOrders.quantityOrdered,
        })
        .from(vipCustomerOrders)
    );

select `product_id`, `quantity_ordered` from `regular_customer_orders`
intersect all 
select `product_id`, `quantity_ordered` from `vip_customer_orders`

import { int, mysqlTable } from "drizzle-orm/mysql-core";

const regularCustomerOrders = mysqlTable('regular_customer_orders', {
    customerId: int('customer_id').primaryKey(),
    productId: int('product_id').notNull(),
    quantityOrdered: int('quantity_ordered').notNull(),
});

const vipCustomerOrders = mysqlTable('vip_customer_orders', {
    customerId: int('customer_id').primaryKey(),
    productId: int('product_id').notNull(),
    quantityOrdered: int('quantity_ordered').notNull(),
});

Except

두 개의 쿼리 블록 A와 B가 있을 때, A의 결과 중 B에도 존재하지 않는 결과만 반환하며, 중복은 제거합니다.

예를 들어, 직원들의 프로젝트 할당 정보를 저장하는 두 개의 테이블이 있다고 가정해 봅시다. 여러분은 한 부서에만 속하고 다른 부서와 공유되지 않는 프로젝트를 찾고 싶습니다. 이때 동일한 프로젝트가 여러 직원에게 할당되어 있어도 중복 없이 한 번만 카운트하고 싶습니다.

이 시나리오에서는 한 부서에만 속하고 다른 부서와 공유되지 않는 프로젝트를 식별하려고 합니다. 동일한 프로젝트가 여러 번 카운트되지 않도록 중복을 제거합니다.

PostgreSQL

MySQL

SQLite

SingleStore

import-pattern

builder-pattern

schema.ts

import { except } from 'drizzle-orm/pg-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB);

const result = await except(departmentACourses, departmentBCourses);

select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"

import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));

select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"

import { integer, pgTable, varchar } from "drizzle-orm/pg-core";

const depA = pgTable('department_a_projects', {
    employeeId: integer('employee_id'),
    projectsName: varchar('projects_name').notNull(),
});

const depB = pgTable('department_b_projects', {
    employeeId: integer('employee_id'),
    projectsName: varchar('projects_name').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { except } from 'drizzle-orm/mysql-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB);

const result = await except(departmentACourses, departmentBCourses);

select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`

import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));

select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`

import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";

const depA = mysqlTable('department_a_projects', {
    employeeId: int('employee_id'),
    projectsName: varchar('projects_name', { length: 256 }).notNull(),
});

const depB = mysqlTable('department_b_projects', {
    employeeId: int('employee_id'),
    projectsName: varchar('projects_name', { length: 256 }).notNull(),
});

import-pattern

builder-pattern

schema.ts

import { except } from 'drizzle-orm/sqlite-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB);

const result = await except(departmentACourses, departmentBCourses);

select "projects_name" from "department_a_projects" 
except 
select "projects_name" from "department_b_projects"

import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));

select "projects_name" from "department_a_projects" 
except 
select "projects_name" from "department_b_projects"

import { int, sqliteTable, text } from "drizzle-orm/sqlite-core";

const depA = sqliteTable('department_a_projects', {
    employeeId: int('employee_id'),
    projectsName: text('projects_name').notNull(),
});

const depB = sqliteTable('department_b_projects', {
    employeeId: int('employee_id'),
    projectsName: text('projects_name').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { except } from 'drizzle-orm/singlestore-core'
import { depA, depB } from './schema'

const departmentACourses = db.select({ courseName: depA.projectsName }).from(depA);
const departmentBCourses = db.select({ courseName: depB.projectsName }).from(depB);

const result = await except(departmentACourses, departmentBCourses);

select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`

import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));

select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`

import { int, mysqlTable, varchar } from "drizzle-orm/singlestore-core";

const depA = mysqlTable('department_a_projects', {
    employeeId: int('employee_id'),
    projectsName: varchar('projects_name', { length: 256 }).notNull(),
});

const depB = mysqlTable('department_b_projects', {
    employeeId: int('employee_id'),
    projectsName: varchar('projects_name', { length: 256 }).notNull(),
});

Except All

두 쿼리 블록 A와 B가 있을 때, A의 결과 중 B에도 존재하지 않는 모든 결과를 반환합니다. 이때 중복된 결과도 포함됩니다.

예를 들어, 고객 주문 데이터를 담고 있는 두 테이블이 있고, 일반 고객만 주문한 제품을 찾고 싶은 경우를 생각해봅시다. VIP 고객이 주문하지 않은 제품을 찾고자 합니다. 이때, 동일한 제품이 여러 일반 고객에 의해 여러 번 주문된 경우에도 수량 정보를 유지하려고 합니다.

이 시나리오에서는 일반 고객만 주문한 제품을 찾고, VIP 고객이 주문하지 않은 제품을 식별합니다. 동일한 제품이 여러 일반 고객에 의해 여러 번 주문된 경우에도 수량 정보를 유지합니다.

PostgreSQL

MySQL

SingleStore

import-pattern

builder-pattern

schema.ts

import { exceptAll } from 'drizzle-orm/pg-core'
import { regularCustomerOrders, vipCustomerOrders } from './schema'

const regularOrders = db.select({ 
    productId: regularCustomerOrders.productId,
    quantityOrdered: regularCustomerOrders.quantityOrdered }
).from(regularCustomerOrders);

const vipOrders = db.select({ 
    productId: vipCustomerOrders.productId,
    quantityOrdered: vipCustomerOrders.quantityOrdered }
).from(vipCustomerOrders);

const result = await exceptAll(regularOrders, vipOrders);

select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"

import { regularCustomerOrders, vipCustomerOrders } from './schema'

const result = await db
    .select({
      productId: regularCustomerOrders.productId,
      quantityOrdered: regularCustomerOrders.quantityOrdered,
    })
    .from(regularCustomerOrders)
    .exceptAll(
      db
        .select({
          productId: vipCustomerOrders.productId,
          quantityOrdered: vipCustomerOrders.quantityOrdered,
        })
        .from(vipCustomerOrders)
    );

select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"

import { integer, pgTable } from "drizzle-orm/pg-core";

const regularCustomerOrders = pgTable('regular_customer_orders', {
    customerId: integer('customer_id').primaryKey(),
    productId: integer('product_id').notNull(),
    quantityOrdered: integer('quantity_ordered').notNull(),
});

const vipCustomerOrders = pgTable('vip_customer_orders', {
    customerId: integer('customer_id').primaryKey(),
    productId: integer('product_id').notNull(),
    quantityOrdered: integer('quantity_ordered').notNull(),
});

import-pattern

builder-pattern

schema.ts

import { exceptAll } from 'drizzle-orm/mysql-core'
import { regularCustomerOrders, vipCustomerOrders } from './schema'

const regularOrders = db.select({ 
    productId: regularCustomerOrders.productId,
    quantityOrdered: regularCustomerOrders.quantityOrdered }
).from(regularCustomerOrders);

const vipOrders = db.select({ 
    productId: vipCustomerOrders.productId,
    quantityOrdered: vipCustomerOrders.quantityOrdered }
).from(vipCustomerOrders);

const result = await exceptAll(regularOrders, vipOrders);

select `product_id`, `quantity_ordered` from `regular_customer_orders`
except all
select `product_id`, `quantity_ordered` from `vip_customer_orders`

import { regularCustomerOrders, vipCustomerOrders } from './schema'

const result = await db
    .select({
      productId: regularCustomerOrders.productId,
      quantityOrdered: regularCustomerOrders.quantityOrdered,
    })
    .from(regularCustomerOrders)
    .exceptAll(
      db
        .select({
          productId: vipCustomerOrders.productId,
          quantityOrdered: vipCustomerOrders.quantityOrdered,
        })
        .from(vipCustomerOrders)
    );

select `product_id`, `quantity_ordered` from `regular_customer_orders`
except all
select `product_id`, `quantity_ordered` from `vip_customer_orders`

const regularCustomerOrders = mysqlTable('regular_customer_orders', {
    customerId: int('customer_id').primaryKey(),
    productId: int('product_id').notNull(),
    quantityOrdered: int('quantity_ordered').notNull(),
});

const vipCustomerOrders = mysqlTable('vip_customer_orders', {
    customerId: int('customer_id').primaryKey(),
    productId: int('product_id').notNull(),
    quantityOrdered: int('quantity_ordered').notNull(),
});