import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import CodeTabs from '@mdx/CodeTabs.astro'; import CodeTab from '@mdx/CodeTab.astro'; import Callout from '@mdx/Callout.astro';

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLite", "SingleStore", "MSSQL", "CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select "name" from "sellers")
union
(select "name" from "customers")
limit $1
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
(select "name" from "sellers")
union
(select "name" from "customers")
limit $1
```
</CodeTab>
<CodeTab>
```typescript copy
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()
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?
```
</CodeTab>
<CodeTab>
```typescript copy
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()
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select "name" from "sellers")
union 
(select "name" from "customers")
limit ?
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
select "name" from "sellers" union select "name" from "customers" limit ?
```
</CodeTab>
<CodeTab>
```typescript copy
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()
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
(select `name` from `sellers`)
union
(select `name` from `customers`)
limit ?
```
</CodeTab>
<CodeTab>
```typescript copy
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()
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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)
).limit(10);
```
```sql
(select [name] from [sellers])
union
(select [name] from [customers])
limit @limit
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
(select [name] from [sellers])
union
(select [name] from [customers])
limit @limit
```
</CodeTab>
<CodeTab>
```typescript copy
import { int, mssqlTable, ntext, nvarchar } from "drizzle-orm/mssql-core";

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

const customers = mssqlTable('customers', {
    id: int().primaryKey(),
    name: nvarchar({ length: 256 }).notNull(),
    city: ntext(),
    email: nvarchar({ length: 256 }).notNull()
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { union } from 'drizzle-orm/cockroach-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);
```
```sql
(select "name" from "sellers")
union
(select "name" from "customers")
limit $1
```
</CodeTab>
<CodeTab>
```ts copy
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);
```
```sql
(select "name" from "sellers")
union
(select "name" from "customers")
limit $1
```
</CodeTab>
<CodeTab>
```typescript copy
import { int4, cockroachTable, text, varchar } from "drizzle-orm/cockroach-core";

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

const customers = cockroachTable('customers', {
    id: int4().primaryKey(),
    name: varchar({ length: 256 }).notNull(),
    city: text(),
    email: varchar({ length: 256 }).notNull()
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLite", "SingleStore", "MSSQL", "CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```typescript copy
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' }),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `transaction_id` from `online_sales`
union all
select `transaction_id` from `in_store_sales`
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
(select `transaction_id` from `online_sales`)
union all 
(select `transaction_id` from `in_store_sales`)
```
</CodeTab>
<CodeTab>
```typescript copy
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' }),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "transaction_id" from "online_sales" 
union all 
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
select "transaction_id" from "online_sales" 
union all 
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```typescript copy
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' }),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <Callout type='warning'> UNION ALL with ORDER BY behavior inconsistent with MySQL: SingleStore parses UNION ALL followed by ORDER BY commands differently from MySQL. In SingleStore, the following query is valid. In MySQL, it is invalid. </Callout> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `transaction_id` from `online_sales`
union all
select `transaction_id` from `in_store_sales`
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
(select `transaction_id` from `online_sales`)
union all 
(select `transaction_id` from `in_store_sales`)
```
</CodeTab>
<CodeTab>
```typescript copy
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' }),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select [transaction_id] from [online_sales])
union all
(select [transaction_id] from [in_store_sales])
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
(select [transaction_id] from [online_sales])
union all
(select [transaction_id] from [in_store_sales])
```
</CodeTab>
<CodeTab>
```typescript copy
import { int, mssqlTable, timestamp } from "drizzle-orm/mssql-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' }),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { unionAll } from 'drizzle-orm/cockroach-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);
```
```sql
select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```ts copy
import { onlineSales, inStoreSales } from './schema'

const result = await db
  .select({ transaction: onlineSales.transactionId })
  .from(onlineSales)
  .unionAll(
    db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
  );
```
```sql
select "transaction_id" from "online_sales"
union all
select "transaction_id" from "in_store_sales"
```
</CodeTab>
<CodeTab>
```typescript copy
import { int4, cockroachTable, text, timestamp, varchar } from "drizzle-orm/cockroach-core";

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

const inStoreSales = cockroachTable('in_store_sales', {
    transactionId: int4('transaction_id').primaryKey(),
    productId: int4('product_id').unique(),
    quantitySold: int4('quantity_sold'),
    saleDate: timestamp('sale_date', { mode: 'date' }),
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLite", "SingleStore", "MSSQL", "CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript copy
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 = mysqlTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
select "course_name" from "department_a_courses" 
intersect 
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
select `projects_name` from `department_a_projects`
intersect
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript copy
import { int, singlestoreTable, varchar } from "drizzle-orm/singlestore-core";

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

const depB = singlestoreTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select `projects_name` from `department_a_projects`)
intersect
(select `projects_name` from `department_b_projects`)
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
(select `projects_name` from `department_a_projects`)
intersect
(select `projects_name` from `department_b_projects`)
```
</CodeTab>
<CodeTab>
```typescript copy
import { int, mssqlTable, varchar } from "drizzle-orm/mssqlTable-core";

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

const depB = mssqlTable('department_b_courses', {
    studentId: int('student_id'),
    courseName: varchar('course_name', { length: 256 }).notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { intersect } from 'drizzle-orm/cockroach-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);
```
```sql
select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
import { depA, depB } from './schema'

const result = await db
  .select({ courseName: depA.courseName })
  .from(depA)
  .intersect(db.select({ courseName: depB.courseName }).from(depB));
```
```sql
select "course_name" from "department_a_courses"
intersect
select "course_name" from "department_b_courses"
```
</CodeTab>
<CodeTab>
```typescript copy
import { int4, cockroachTable, varchar } from "drizzle-orm/cockroach-core";

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

const depB = cockroachTable('department_b_courses', {
    studentId: int4('student_id'),
    courseName: varchar('course_name').notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLlite", "SingleStore", "MSSQL" ,"CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
```ts copy
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)
    );
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `product_id`, `quantity_ordered` from `regular_customer_orders`
intersect all
select `product_id`, `quantity_ordered` from `vip_customer_orders`
```
</CodeTab>
<CodeTab>
```ts copy
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)
    );
```
```sql
select `product_id`, `quantity_ordered` from `regular_customer_orders`
intersect all 
select `product_id`, `quantity_ordered` from `vip_customer_orders`
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> Not supported by SQLite </Tab> <Tab> Not supported by SingleStore </Tab> <Tab> Not supported by MSSQL </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { intersectAll } from 'drizzle-orm/cockroach-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);
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
```ts copy
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)
    );
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
intersect all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
<CodeTab>
```typescript copy
import { int4, cockroachTable } from "drizzle-orm/cockroach-core";

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

const vipCustomerOrders = cockroachTable('vip_customer_orders', {
    customerId: int4('customer_id').primaryKey(),
    productId: int4('product_id').notNull(),
    quantityOrdered: int4('quantity_ordered').notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLite", "SingleStore", "MSSQL", "CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript 
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "projects_name" from "department_a_projects" 
except 
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
select "projects_name" from "department_a_projects" 
except 
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
select `projects_name` from `department_a_projects`
except
select `projects_name` from `department_b_projects`
```
</CodeTab>
<CodeTab>
```typescript 
import { int, singlestoreTable, varchar } from "drizzle-orm/singlestore-core";

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

const depB = singlestoreTable('department_b_projects', {
    employeeId: int('employee_id'),
    projectsName: varchar('projects_name', { length: 256 }).notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
(select `projects_name` from `department_a_projects`)
except
(select `projects_name` from `department_b_projects`)
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
(select `projects_name` from `department_a_projects`)
except
(select `projects_name` from `department_b_projects`)
```
</CodeTab>
<CodeTab>
```typescript 
import { int, mssqlTable, nvarchar } from "drizzle-orm/mssql-core";

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

const depB = mssqlTable('department_b_projects', {
    employeeId: int('employee_id'),
    projectsName: nvarchar('projects_name', { length: 256 }).notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { except } from 'drizzle-orm/cockroach-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);
```
```sql
select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```ts copy
import { depA, depB } from './schema'

const result = await db
    .select({ courseName: depA.projectsName })
    .from(depA)
    .except(db.select({ courseName: depB.projectsName }).from(depB));
```
```sql
select "projects_name" from "department_a_projects"
except
select "projects_name" from "department_b_projects"
```
</CodeTab>
<CodeTab>
```typescript copy
import { int4, cockroachTable, varchar } from "drizzle-orm/cockroach-core";

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

const depB = cockroachTable('department_b_projects', {
    employeeId: int4('employee_id'),
    projectsName: varchar('projects_name').notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>

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.

<Tabs items={["PostgreSQL", "MySQL", "SQLite", "SingleStore", "MSSQL", "CockroachDB"]}> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
<CodeTab>
```ts copy
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)
    );
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy 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);
```
```sql
select `product_id`, `quantity_ordered` from `regular_customer_orders`
except all
select `product_id`, `quantity_ordered` from `vip_customer_orders`
```
</CodeTab>
<CodeTab>
```ts copy
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)
    );
```
```sql
select `product_id`, `quantity_ordered` from `regular_customer_orders`
except all
select `product_id`, `quantity_ordered` from `vip_customer_orders`
```
</CodeTab>
<CodeTab>
```typescript copy
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(),
});
```
</CodeTab>

</CodeTabs> </Tab> <Tab> Not supported by SQLite </Tab> <Tab> Not supported by SingleStore </Tab> <Tab> Not supported by MSSQL </Tab> <Tab> <CodeTabs items={["import-pattern", "builder-pattern", "schema.ts"]}> <CodeTab> ```typescript copy import { exceptAll } from 'drizzle-orm/cockroach-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);
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
<CodeTab>
```ts copy
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)
    );
```
```sql
select "product_id", "quantity_ordered" from "regular_customer_orders"
except all
select "product_id", "quantity_ordered" from "vip_customer_orders"
```
</CodeTab>
<CodeTab>
```typescript copy
import { int4, cockroachTable } from "drizzle-orm/cockroach-core";

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

const vipCustomerOrders = cockroachTable('vip_customer_orders', {
    customerId: int4('customer_id').primaryKey(),
    productId: int4('product_id').notNull(),
    quantityOrdered: int4('quantity_ordered').notNull(),
});
```
</CodeTab>

</CodeTabs> </Tab> </Tabs>