Set Operations

SQL set operations combine the results of multiple query blocks into a single result. The SQL standard defines the following three set operations: UNION, INTERSECT, EXCEPT, UNION ALL.

Union

Combine all results from two query blocks into a single result, omitting any duplicates.

Get all names from customers and users tables without duplicates.

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

Combine all results from two query blocks into a single result, with duplicates.

Let’s consider a scenario where you have two tables, one representing online sales and the other representing in-store sales. In this case, you want to combine the data from both tables into a single result set. Since there might be duplicate transactions, you want to keep all the records and not eliminate duplicates.

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

Combine only those rows which the results of two query blocks have in common, omitting any duplicates.

Suppose you have two tables that store information about students’ course enrollments. You want to find the courses that are common between two different departments, but you want distinct course names, and you’re not interested in counting multiple enrollments of the same course by the same student.

In this scenario, you want to find courses that are common between the two departments but don’t want to count the same course multiple times even if multiple students from the same department are enrolled in it.

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

For two query blocks A and B, return all results from A which are not also present in B, omitting any duplicates.

Suppose you have two tables that store information about employees’ project assignments. You want to find the projects that are unique to one department and not shared with another department, excluding duplicates.

In this scenario, you want to identify the projects that are exclusive to one department and not shared with the other department. You don’t want to count the same project multiple times, even if multiple employees from the same department are assigned to it.

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