Select¶
This page provides an in-depth explanation of how to construct and customize SELECT statements using ts-sql-query. It covers advanced projection strategies, optional value handling, dynamic ordering, inline subqueries, and compound queries. Each section includes examples, generated SQL, and the resulting TypeScript types to help understand the mapping and behavior.
Select with joins and order by¶
const firstName = 'John';
const lastName = null;
const company = tCompany.as('comp');
const customersWithCompanyName = connection.selectFrom(tCustomer)
.innerJoin(company).on(tCustomer.companyId.equals(company.id))
.where(tCustomer.firstName.startsWithInsensitive(firstName))
.and(tCustomer.lastName.startsWithInsensitiveIfValue(lastName))
.select({
id: tCustomer.id,
firstName: tCustomer.firstName,
lastName: tCustomer.lastName,
birthday: tCustomer.birthday,
companyName: company.name
})
.orderBy('firstName', 'insensitive')
.orderBy('lastName', 'asc insensitive')
.executeSelectMany();
The executed query is:
select
customer.id as id,
customer.first_name as firstName,
customer.last_name as lastName,
customer.birthday as birthday,
comp.name as companyName
from customer
inner join company as comp on customer.company_id = comp.id
where lower(customer.first_name) like concat(lower(?), '%')
order by
lower(firstName),
lower(lastName) asc
select
customer.id as id,
customer.first_name as firstName,
customer.last_name as lastName,
customer.birthday as birthday,
comp.`name` as companyName
from customer
inner join company as comp on customer.company_id = comp.id
where lower(customer.first_name) like concat(lower(?), '%')
order by
lower(firstName),
lower(lastName) asc
select
customer.id as "id",
customer.first_name as "firstName",
customer.last_name as "lastName",
customer.birthday as "birthday",
comp.name as "companyName"
from customer
inner join company comp on customer.company_id = comp.id
where lower(customer.first_name) like lower(:0 || '%') escape '\\'
order by
lower("firstName"),
lower("lastName") asc
select
customer.id as id,
customer.first_name as "firstName",
customer.last_name as "lastName",
customer.birthday as birthday,
comp.name as "companyName"
from customer
inner join company as comp on customer.company_id = comp.id
where customer.first_name ilike ($1 || '%')
order by
lower("firstName"),
lower("lastName") asc
select
customer.id as id,
customer.first_name as firstName,
customer.last_name as lastName,
customer.birthday as birthday,
comp.name as companyName
from customer
inner join company as comp on customer.company_id = comp.id
where lower(customer.first_name) like lower(? || '%') escape '\\'
order by
lower(firstName),
lower(lastName) asc
select
customer.id as id,
customer.first_name as firstName,
customer.last_name as lastName,
customer.birthday as birthday,
comp.name as companyName
from customer
inner join company as comp on customer.company_id = comp.id
where lower(customer.first_name) like lower(@0 + '%')
order by
lower(firstName),
lower(lastName) asc
The parameters are: [ 'John' ]
The result type is:
const customersWithCompanyName: Promise<{
id: number;
firstName: string;
lastName: string;
companyName: string;
birthday?: Date;
}[]>
The name of the columns to order corresponds to the name/path in the query's result. The supported order by modes are:
type OrderByMode = 'asc' | 'desc' | 'asc nulls first' | 'asc nulls last' | 'desc nulls first' | 'desc nulls last' | 'insensitive' |
'asc insensitive' | 'desc insensitive' | 'asc nulls first insensitive' | 'asc nulls last insensitive' |
'desc nulls first insensitive' | 'desc nulls last insensitive'
Note
For the databases that don't support null first or null last, a proper order by that emulates that behaviour is generated. The insensitive modifier makes the ordering key-insensitive according to the insensitive strategy defined in your connection.
In case the insensitive modifier is used in a not string column, the modifier will be just ignored.
Tip
You can project optional values in objects as always-required properties that allow null by calling projectingOptionalValuesAsNullable() immediately after select(...). This transformation only affects the resulting TypeScript type — the generated SQL remains unchanged. Instead of using optional fields (?), all properties are treated as required but typed as potentially null, which can simplify downstream type checks when dealing with partial or outer-joined data.
Select ordering by a not returned column¶
const customerId = 10;
const customerWithId = connection.selectFrom(tCustomer)
.where(tCustomer.id.equals(customerId))
.select({
id: tCustomer.id,
firstName: tCustomer.firstName,
lastName: tCustomer.lastName
})
.orderBy(tCustomer.birthday, 'desc nulls last')
.executeSelectOne();
The executed query is:
The parameters are: [ 10 ]
The result type is:
Select with subquery and dynamic order by¶
const orderBy = 'customerFirstName asc nulls first, customerLastName';
const customerWithSelectedCompanies = connection.selectFrom(tCustomer)
.where(tCustomer.companyId.in(
connection.selectFrom(tCompany)
.where(tCompany.name.contains('Cia.'))
.selectOneColumn(tCompany.id)
)).select({
customerId: tCustomer.id,
customerFirstName: tCustomer.firstName,
customerLastName: tCustomer.lastName
}).orderByFromString(orderBy)
.executeSelectMany();
The executed query is:
The parameters are: [ 'Cia.' ]
The result type is:
const customerWithSelectedCompanies: Promise<{
customerId: number;
customerFirstName: string;
customerLastName: string;
}[]>
Select with aggregate functions and group by¶
const customerCountPerCompany = connection.selectFrom(tCompany)
.innerJoin(tCustomer).on(tCustomer.companyId.equals(tCompany.id))
.groupBy(tCompany.id, tCompany.name)
.select({
companyId: tCompany.id,
companyName: tCompany.name,
customerCount: connection.count(tCustomer.id)
})
.executeSelectMany();
The executed query is:
The parameters are: []
The result type is:
const customerCountPerCompany: Promise<{
companyId: number;
companyName: string;
customerCount: number;
}[]>
Select with left join¶
To use a table or view in a left join, you must create a left join representation first by calling the method forUseInLeftJoin or forUseInLeftJoinAs.
const parent = tCompany.forUseInLeftJoinAs('parent');
const leftJoinCompany = connection.selectFrom(tCompany)
.leftJoin(parent).on(tCompany.parentId.equals(parent.id))
.select({
id: tCompany.id,
name: tCompany.name,
parentId: parent.id,
parentName: parent.name
}).executeSelectMany();
The executed query is:
The parameters are: []
The result type is:
const leftJoinCompany: Promise<{
id: number;
name: string;
parentId?: number;
parentName?: string;
}[]>
Select with left join and complex projections¶
When you are doing a left join, you probably want to use Complex projections:
const parent = tCompany.forUseInLeftJoinAs('parent');
const leftJoinCompany = await connection.selectFrom(tCompany)
.leftJoin(parent).on(tCompany.parentId.equals(parent.id))
.select({
id: tCompany.id,
name: tCompany.name,
parent: {
id: parent.id,
name: parent.name
}
}).executeSelectMany();
The executed query is:
The parameters are: []
The result type is:
const leftJoinCompany: Promise<{
id: number;
name: string;
parent?: {
id: number;
name: string;
};
}[]>
Select with a compound operator (union, intersect, except)¶
const allDataWithName = connection.selectFrom(tCustomer)
.select({
id: tCustomer.id,
name: tCustomer.firstName.concat(' ').concat(tCustomer.lastName),
type: connection.const<'customer' | 'company'>('customer', 'enum', 'customerOrCompany')
}).unionAll(
connection.selectFrom(tCompany)
.select({
id: tCompany.id,
name: tCompany.name,
type: connection.const<'customer' | 'company'>('company', 'enum', 'customerOrCompany')
})
).executeSelectMany();
The executed query is:
The parameters are: [ ' ', 'customer', 'company' ]
The result type is:
Warning
All combined queries in a compound operator must return the same number of columns with compatible types and matching column names.
Note
Depending on your database, the supported compound operators are: union, unionAll, intersect, intersectAll, except, exceptAll, minus (alias for except), minusAll (alias for exceptAll)
Using a select as a view in another select query (SQL with clause)¶
You can define a select query and use it as if it were a view in another select query.
To allow it, you must call the forUseInQueryAs method instead of executing the query. This returns a view-like representation that will be included as a WITH clause in the final SQL, using the name passed to forUseInQueryAs.
const customerCountPerCompanyWith = connection.selectFrom(tCompany)
.innerJoin(tCustomer).on(tCustomer.companyId.equals(tCompany.id))
.select({
companyId: tCompany.id,
companyName: tCompany.name,
customerCount: connection.count(tCustomer.id)
}).groupBy('companyId', 'companyName')
.forUseInQueryAs('customerCountPerCompany');
const customerCountPerAcmeCompanies = connection.selectFrom(customerCountPerCompanyWith)
.where(customerCountPerCompanyWith.companyName.containsInsensitive('ACME'))
.select({
acmeCompanyId: customerCountPerCompanyWith.companyId,
acmeCompanyName: customerCountPerCompanyWith.companyName,
acmeCustomerCount: customerCountPerCompanyWith.customerCount
})
.executeSelectMany();
The executed query is:
with
customerCountPerCompany as (
select
company.id as companyId,
company.name as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.name
)
select
companyId as acmeCompanyId,
companyName as acmeCompanyName,
customerCount as acmeCustomerCount
from customerCountPerCompany
where lower(companyName) like concat('%', lower(?), '%')
with
customerCountPerCompany as (
select
company.id as companyId,
company.`name` as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.`name`
)
select
companyId as acmeCompanyId,
companyName as acmeCompanyName,
customerCount as acmeCustomerCount
from customerCountPerCompany
where lower(companyName) like concat('%', lower(?), '%')
with
customerCountPerCompany as (
select
company.id as companyId,
company.name as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.name
)
select
companyId as "acmeCompanyId",
companyName as "acmeCompanyName",
customerCount as "acmeCustomerCount"
from customerCountPerCompany
where lower(companyName) like lower('%' || :0 || '%') escape '\\'
with
customerCountPerCompany as (
select
company.id as companyId,
company.name as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.name
)
select
companyId as "acmeCompanyId",
companyName as "acmeCompanyName",
customerCount as "acmeCustomerCount"
from customerCountPerCompany
where companyName ilike ('%' || $1 || '%')
with
customerCountPerCompany as (
select
company.id as companyId,
company.name as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.name
)
select
companyId as acmeCompanyId,
companyName as acmeCompanyName,
customerCount as acmeCustomerCount
from customerCountPerCompany
where lower(companyName) like lower('%' || ? || '%') escape '\\'
with
customerCountPerCompany as (
select
company.id as companyId,
company.name as companyName,
count(customer.id) as customerCount
from company
inner join customer on customer.company_id = company.id
group by
company.id,
company.name
)
select
companyId as acmeCompanyId,
companyName as acmeCompanyName,
customerCount as acmeCustomerCount
from customerCountPerCompany
where lower(companyName) like lower('%' + @0 + '%')
The parameters are: [ 'ACME' ]
The result type is:
const customerCountPerAcmeCompanies: Promise<{
acmeCompanyId: number;
acmeCompanyName: string;
acmeCustomerCount: number;
}[]>
Select count all¶
const companyId = 10;
const numberOfCustomers = connection.selectFrom(tCustomer)
.where(tCustomer.companyId.equals(companyId))
.selectCountAll() // Shortcut to .selectOneColumn(connection.countAll())
.executeSelectOne();
The executed query is:
The parameters are: [ 10 ]
The result type is:
Inline subquery as value¶
To use a select query that returns one column as an inline query value, you must get the value representation by calling the method forUseAsInlineQueryValue and then use the value representation as with any other value in a secondary query.
const acmeId = connection.selectFrom(tCompany)
.where(tCompany.name.equals('ACME'))
.selectOneColumn(tCompany.id)
.forUseAsInlineQueryValue();
const acmeCustomers = connection.selectFrom(tCustomer)
.where(tCustomer.companyId.equals(acmeId))
.select({
id: tCustomer.id,
name: tCustomer.firstName.concat(' ').concat(tCustomer.lastName)
})
.executeSelectMany();
The executed query is:
The parameters are: [ ' ', 'ACME' ]
The result type is:
Inline subquery referencing outer query¶
To use a select query that returns one column as an inline query value that references the outer query's tables, you must start the subquery calling subSelectUsing and providing by argument the external tables or views required to execute the subquery, and then get the value representation, in the end, by calling the method forUseAsInlineQueryValue and then use the value representation as with any other value in the outer query.
const numberOfCustomers = connection
.subSelectUsing(tCompany)
.from(tCustomer)
.where(tCustomer.companyId.equals(tCompany.id))
.selectCountAll()
.forUseAsInlineQueryValue(); // At this point is a value that you can use in other query
const companiesWithNumberOfCustomers = connection.selectFrom(tCompany)
.select({
id: tCompany.id,
name: tCompany.name,
numberOfCustomers: numberOfCustomers
})
.executeSelectMany();
The executed query is:
The parameters are: [ ]
The result type is:
const companiesWithNumberOfCustomers: Promise<{
id: number;
name: string;
numberOfCustomers: number;
}[]>
Tip
In the previous example, it is more convenient to use .selectCountAll() instead of .selectOneColumn(connection.countAll()) because the returned value will not be optional; if not, you will not need to use .selectOneColumn(connection.countAll()).valueWhenNull(0) to achive same optionality result.
Select clauses order¶
The order of clauses in a select query must follow one of the supported logical patterns:
Warning
These orders are enforced by the library to ensure SQL correctness across databases. Below is a list of valid clause orders.
- Logical order: from, join, WHERE, group by, having, select, order by, limit, offset, customizeQuery
- Alternative logical order 1: from, join, group by, having, WHERE, select, order by, limit, offset, customizeQuery
- Alternative logical order 2: from, join, group by, having, select, WHERE, order by, limit, offset, customizeQuery
- Alternative logical order 3: from, join, group by, having, select, order by, WHERE, limit, offset, customizeQuery
- Alternative logical order 4: from, join, group by, having, select, order by, limit, offset, WHERE, customizeQuery
-
Alternative logical order 5: from, join, group by, having, select, order by, limit, offset, customizeQuery, WHERE
-
Alternative order 1: from, join, select, WHERE, group by, having, order by, limit, offset, customizeQuery
- Alternative order 2: from, join, select, group by, having, WHERE, order by, limit, offset, customizeQuery
- Alternative order 3: from, join, select, group by, having, order by, WHERE, limit, offset, customizeQuery
- Alternative order 4: from, join, select, group by, having, order by, limit, offset, WHERE, customizeQuery
- Alternative order 5: from, join, select, group by, having, order by, limit, offset, customizeQuery, WHERE
Oracle variants
Oracle support start with, connect by and ordering siblings only. The ordering siblings only modifier changes the previous order by to order siblings by.
- Logical order: from, join, start with, connect by, WHERE, group by, having, select, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative logical order 0: from, join, WHERE, start with, connect by, group by, having, select, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative logical order 1: from, join, start with, connect by, group by, having, WHERE, select, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative logical order 2: from, join, start with, connect by, group by, having, select, WHERE, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative logical order 3: from, join, start with, connect by, group by, having, select, order by, ordering siblings only, WHERE, limit, offset, customizeQuery
- Alternative logical order 4: from, join, start with, connect by, group by, having, select, order by, ordering siblings only, limit, offset, WHERE, customizeQuery
-
Alternative logical order 5: from, join, start with, connect by, group by, having, select, order by, ordering siblings only, limit, offset, customizeQuery, WHERE
-
Alternative order 1: from, join, start with, connect by, select, WHERE, group by, having, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative order 2: from, join, start with, connect by, select, group by, having, WHERE, order by, ordering siblings only, limit, offset, customizeQuery
- Alternative order 3: from, join, start with, connect by, select, group by, having, order by, ordering siblings only, WHERE, limit, offset, customizeQuery
- Alternative order 4: from, join, start with, connect by, select, group by, having, order by, ordering siblings only, limit, offset, WHERE, customizeQuery
- Alternative order 5: from, join, start with, connect by, select, group by, having, order by, ordering siblings only, limit, offset, customizeQuery, WHERE
-
Logical order (Oracle variant): from, join, WHERE, start with, connect by, group by, having, select, order by, ordering siblings only, limit, offset, customizeQuery
-
Second alternative order 0: from, join, select, WHERE, start with, connect by, group by, having, order by, ordering siblings only, limit, offset, customizeQuery
- Second alternative order 1: from, join, select, start with, connect by, WHERE, group by, having, order by, ordering siblings only, limit, offset, customizeQuery
- Second alternative order 2: from, join, select, start with, connect by, group by, having, WHERE, order by, ordering siblings only, limit, offset, customizeQuery
- Second alternative order 3: from, join, select, start with, connect by, group by, having, order by, ordering siblings only, WHERE, limit, offset, customizeQuery
- Second alternative order 4: from, join, select, start with, connect by, group by, having, order by, ordering siblings only, limit, offset, WHERE, customizeQuery
- Second alternative order 5: from, join, select, start with, connect by, group by, having, order by, ordering siblings only, limit, offset, customizeQuery, WHERE