Select and Clause Builders¶
from sqlspec.builder import (
Case,
CaseBuilder,
CommonTableExpressionMixin,
HavingClauseMixin,
JoinBuilder,
LimitOffsetClauseMixin,
OrderByClauseMixin,
PivotClauseMixin,
Select,
SelectClauseMixin,
SetOperationMixin,
SubqueryBuilder,
UnpivotClauseMixin,
WhereClauseMixin,
WindowFunctionBuilder,
)
from sqlspec.builder._join import JoinClauseMixin
Fluent SELECT query builder supporting joins, CTEs, window functions, CASE expressions, subqueries, PIVOT/UNPIVOT, and set operations (UNION, INTERSECT, EXCEPT). See Query Builder: SELECT, Filtering and Joins and Expressions, Window Functions and Vector Search for usage examples.
Select Builder¶
Select
¶
Bases: QueryBuilder, WhereClauseMixin, OrderByClauseMixin, LimitOffsetClauseMixin, SelectClauseMixin, JoinClauseMixin, HavingClauseMixin, SetOperationMixin, CommonTableExpressionMixin, PivotClauseMixin, UnpivotClauseMixin, ExplainMixin
Builder for SELECT queries.
Provides a fluent interface for constructing SQL SELECT statements with parameter binding and validation.
with_hint(hint, *, location='statement', table=None, dialect=None)
¶
Attach an optimizer or dialect-specific hint to the query.
for_update(*, skip_locked=False, nowait=False, of=None)
¶
Add FOR UPDATE clause to SELECT statement for row-level locking.
for_share(*, skip_locked=False, nowait=False, of=None)
¶
Add FOR SHARE clause for shared row-level locking.
for_key_share()
¶
Add FOR KEY SHARE clause (PostgreSQL-specific).
FOR KEY SHARE is like FOR SHARE, but the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.
for_no_key_update()
¶
Add FOR NO KEY UPDATE clause (PostgreSQL-specific).
FOR NO KEY UPDATE is like FOR UPDATE, but the lock is weaker: it does not block SELECT FOR KEY SHARE commands that attempt to acquire a share lock on the same rows.
Join, Window, Case and Subquery Builders¶
JoinBuilder
¶
WindowFunctionBuilder
¶
CaseBuilder
¶
Fluent builder for CASE expressions used within SELECT clauses.
Case
¶
Bases: ExpressionWrapper
Represent a SQL CASE expression with structured components.
Attributes:
| Name | Type | Description |
|---|---|---|
conditions |
|
|
default |
|
|
expression |
Case
|
|
SubqueryBuilder
¶
Helper to build subquery expressions for EXISTS/IN/ANY/ALL operations.
Fragments built from a parameterized subquery builder carry its parameters in
expression metadata; .where(), .having(), and Column comparisons merge
them into the outer query.