AbstractReadonly_Protected Optionalcache// Select all users, each user with every pet
const usersWithPets: { user: User; pets: Pet; }[] = await db.select()
.from(users)
.crossJoin(pets)
// Select userId and petId
const usersIdsAndPetIds: { userId: number; petId: number; }[] = await db.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.crossJoin(pets)
ProtecteddialectAdds except set operator to the query.
Calling this method will retrieve all unique rows from the left query, except for the rows that are present in the result set of the right query.
See docs: https://orm.drizzle.team/docs/set-operations#except
// Select all courses offered in department A but not in department B
await db.select({ courseName: depA.courseName })
.from(depA)
.except(
db.select({ courseName: depB.courseName }).from(depB)
);
// or
import { except } from 'drizzle-orm/sqlite-core'
await except(
db.select({ courseName: depA.courseName }).from(depA),
db.select({ courseName: depB.courseName }).from(depB)
);
Executes a full join operation by combining rows from two tables into a new table.
Calling this method retrieves all rows from both main and joined tables, merging rows with matching values and filling in null for non-matching columns.
// Select all users and their pets
const usersWithPets: { user: User | null; pets: Pet | null; }[] = await db.select()
.from(users)
.fullJoin(pets, eq(users.id, pets.ownerId))
// Select userId and petId
const usersIdsAndPetIds: { userId: number | null; petId: number | null; }[] = await db.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.fullJoin(pets, eq(users.id, pets.ownerId))
Executes an inner join operation, creating a new table by combining rows from two tables that have matching values.
Calling this method retrieves rows that have corresponding entries in both joined tables. Rows without matching entries in either table are excluded, resulting in a table that includes only matching pairs.
// Select all users and their pets
const usersWithPets: { user: User; pets: Pet; }[] = await db.select()
.from(users)
.innerJoin(pets, eq(users.id, pets.ownerId))
// Select userId and petId
const usersIdsAndPetIds: { userId: number; petId: number; }[] = await db.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.innerJoin(pets, eq(users.id, pets.ownerId))
Adds intersect set operator to the query.
Calling this method will retain only the rows that are present in both result sets and eliminate duplicates.
See docs: https://orm.drizzle.team/docs/set-operations#intersect
// Select course names that are offered in both departments A and B
await db.select({ courseName: depA.courseName })
.from(depA)
.intersect(
db.select({ courseName: depB.courseName }).from(depB)
);
// or
import { intersect } from 'drizzle-orm/sqlite-core'
await intersect(
db.select({ courseName: depA.courseName }).from(depA),
db.select({ courseName: depB.courseName }).from(depB)
);
ProtectedjoinsExecutes a left join operation by adding another table to the current query.
Calling this method associates each row of the table with the corresponding row from the joined table, if a match is found. If no matching row exists, it sets all columns of the joined table to null.
// Select all users and their pets
const usersWithPets: { user: User; pets: Pet | null; }[] = await db.select()
.from(users)
.leftJoin(pets, eq(users.id, pets.ownerId))
// Select userId and petId
const usersIdsAndPetIds: { userId: number; petId: number | null; }[] = await db.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.leftJoin(pets, eq(users.id, pets.ownerId))
Executes a right join operation by adding another table to the current query.
Calling this method associates each row of the joined table with the corresponding row from the main table, if a match is found. If no matching row exists, it sets all columns of the main table to null.
// Select all users and their pets
const usersWithPets: { user: User | null; pets: Pet; }[] = await db.select()
.from(users)
.rightJoin(pets, eq(users.id, pets.ownerId))
// Select userId and petId
const usersIdsAndPetIds: { userId: number | null; petId: number; }[] = await db.select({
userId: users.id,
petId: pets.id,
})
.from(users)
.rightJoin(pets, eq(users.id, pets.ownerId))
ProtectedsessionAdds union set operator to the query.
Calling this method will combine the result sets of the select statements and remove any duplicate rows that appear across them.
See docs: https://orm.drizzle.team/docs/set-operations#union
// Select all unique names from customers and users tables
await db.select({ name: users.name })
.from(users)
.union(
db.select({ name: customers.name }).from(customers)
);
// or
import { union } from 'drizzle-orm/sqlite-core'
await union(
db.select({ name: users.name }).from(users),
db.select({ name: customers.name }).from(customers)
);
Adds union all set operator to the query.
Calling this method will combine the result-set of the select statements and keep all duplicate rows that appear across them.
See docs: https://orm.drizzle.team/docs/set-operations#union-all
// Select all transaction ids from both online and in-store sales
await db.select({ transaction: onlineSales.transactionId })
.from(onlineSales)
.unionAll(
db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
);
// or
import { unionAll } from 'drizzle-orm/sqlite-core'
await unionAll(
db.select({ transaction: onlineSales.transactionId }).from(onlineSales),
db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales)
);
ProtectedusedStatic Readonly[entityAbstractgetAdds a group by clause to the query.
Calling this method will group rows that have the same values into summary rows, often used for aggregation purposes.
Adds a group by clause to the query.
Calling this method will group rows that have the same values into summary rows, often used for aggregation purposes.
Adds a having clause to the query.
Calling this method will select only those rows that fulfill a specified condition. It is typically used with aggregate functions to filter the aggregated data based on a specified condition.
the having clause.
Adds a limit clause to the query.
Calling this method will set the maximum number of rows that will be returned by this query.
See docs: https://orm.drizzle.team/docs/select#limit--offset
the limit clause.
Adds an offset clause to the query.
Calling this method will skip a number of rows when returning results from this query.
See docs: https://orm.drizzle.team/docs/select#limit--offset
the offset clause.
Adds an order by clause to the query.
Calling this method will sort the result-set in ascending or descending order. By default, the sort order is ascending.
// Select cars ordered by year
await db.select().from(cars).orderBy(cars.year);
You can specify whether results are in ascending or descending order with the asc() and desc() operators.
// Select cars ordered by year in descending order
await db.select().from(cars).orderBy(desc(cars.year));
// Select cars ordered by year and price
await db.select().from(cars).orderBy(asc(cars.year), desc(cars.price));
Adds an order by clause to the query.
Calling this method will sort the result-set in ascending or descending order. By default, the sort order is ascending.
// Select cars ordered by year
await db.select().from(cars).orderBy(cars.year);
You can specify whether results are in ascending or descending order with the asc() and desc() operators.
// Select cars ordered by year in descending order
await db.select().from(cars).orderBy(desc(cars.year));
// Select cars ordered by year and price
await db.select().from(cars).orderBy(asc(cars.year), desc(cars.price));
Adds a where clause to the query.
Calling this method will select only those rows that fulfill a specified condition.
the where clause.
You can use conditional operators and sql function to filter the rows to be selected.
// Select all cars with green color
await db.select().from(cars).where(eq(cars.color, 'green'));
// or
await db.select().from(cars).where(sql`${cars.color} = 'green'`)
You can logically combine conditional operators with and() and or() operators:
// Select all BMW cars with a green color
await db.select().from(cars).where(and(eq(cars.color, 'green'), eq(cars.brand, 'BMW')));
// Select all cars with the green or blue color
await db.select().from(cars).where(or(eq(cars.color, 'green'), eq(cars.color, 'blue')));
Executes a
cross joinoperation by combining rows from two tables into a new table.Calling this method retrieves all rows from both main and joined tables, merging all rows from each table.
See docs: https://orm.drizzle.team/docs/joins#cross-join