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, INTERSECT ALL, EXCEPT 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.

PostgreSQL

MySQL

SQLite

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

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.

PostgreSQL

MySQL

SQLite

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

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.

PostgreSQL

MySQL

SQLite

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

Intersect All

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

Let’s consider a scenario where you have two tables containing data about customer orders, and you want to identify products that are ordered by both regular customers and VIP customers. In this case, you want to keep track of the quantity of each product, even if it’s ordered multiple times by different customers.

In this scenario, you want to find products that are ordered by both regular customers and VIP customers, but you want to retain the quantity information, even if the same product is ordered multiple times by different customers.

PostgreSQL

MySQL

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

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.

PostgreSQL

MySQL

SQLite

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

Except All

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

Let’s consider a scenario where you have two tables containing data about customer orders, and you want to identify products that are exclusively ordered by regular customers (without VIP customers). In this case, you want to keep track of the quantity of each product, even if it’s ordered multiple times by different regular customers.

In this scenario, you want to find products that are exclusively ordered by regular customers and not ordered by VIP customers. You want to retain the quantity information, even if the same product is ordered multiple times by different regular customers.

PostgreSQL

MySQL

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