集合运算

SQL 集合运算将多个查询块的结果合并为单个结果。
SQL 标准定义了以下四种集合运算：UNION、INTERSECT、EXCEPT、UNION ALL。

Union

将两个查询块中的所有结果合并为单个结果，并省略任何重复项。

获取 customers 和 users 表中的所有名称，不包含重复值。

import-pattern

builder-pattern

schema.ts

import { union } from 'drizzle-orm/mssql-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)
);

(select [name] from [sellers])
union
(select [name] from [customers])

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

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

(select [name] from [sellers])
union
(select [name] from [customers])

import { int, mssqlTable, text, varchar } from "drizzle-orm/mssql-core";

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

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

Union All

将两个查询块中的所有结果合并为单个结果，并保留重复项。

假设你有两张表，一张表示线上销售，另一张表示店内销售。在这种情况下，你希望将这两张表中的数据合并为一个结果集。由于可能存在重复交易，
你希望保留所有记录，而不是去重。

import-pattern

builder-pattern

schema.ts

import { unionAll } from 'drizzle-orm/mssql-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, mssqlTable, text, datetime2, varchar } from "drizzle-orm/mssql-core";

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

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

Intersect

仅合并两个查询块结果中共有的那些行，并省略任何重复项。

假设你有两张表，存储学生课程选修信息。
你想找出两个不同院系之间共有的课程，
但你只想要不同的课程名称，并且不关心同一学生对同一课程的多次
选课记录。

在这个场景中，你想找出两个院系之间共有的课程，但不希望
即使同一院系的多个学生选了同一门课，也把这门课统计多次。

import-pattern

builder-pattern

schema.ts

import { intersect } from 'drizzle-orm/mssql-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, mssqlTable, varchar } from "drizzle-orm/mssql-core";

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

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

Except

对于两个查询块 A 和 B，返回 A 中所有不同时存在于 B 中的结果，并省略任何重复项。

假设你有两张表，存储员工项目分配信息。
你想找出某个部门独有、且未与另一个部门共享的项目，并排除重复项。

在这个场景中，你想识别某个部门独有、且
未与另一个部门共享的项目。你不希望把同一个项目
统计多次，即使同一部门的多个员工被分配到了它。

import-pattern

builder-pattern

schema.ts

import { except } from 'drizzle-orm/mssql-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, mssqlTable, varchar } from "drizzle-orm/mssql-core";

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

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