Skip to content

Select & Clause Builders

from sqlspec.builder import (
    Case,
    CaseBuilder,
    CommonTableExpressionMixin,
    HavingClauseMixin,
    JoinBuilder,
    LimitOffsetClauseMixin,
    OrderByClauseMixin,
    PivotClauseMixin,
    Select,
    SelectClauseMixin,
    SetOperationMixin,
    SubqueryBuilder,
    UnpivotClauseMixin,
    WhereClauseMixin,
    WindowFunctionBuilder,
)

Fluent SELECT query builder supporting joins, CTEs, window functions, CASE expressions, subqueries, PIVOT/UNPIVOT, and set operations (UNION, INTERSECT, EXCEPT). See the Query Builder Guide 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 

with_hint(hint, *, location='statement', table=None, dialect=None)

Attach an optimizer or dialect-specific hint to the query.

Parameters:

Name Type Description Default
hint str

The raw hint string.

 required
location str

Where to apply the hint ('statement', 'table').

 'statement'
table str | None

Table name if the hint is for a specific table.

 None
dialect str | None

Restrict the hint to a specific dialect (optional).

 None

Returns:

Type Description
Self

The current builder instance for method chaining.

for_update 

for_update(*, skip_locked=False, nowait=False, of=None)

Add FOR UPDATE clause to SELECT statement for row-level locking.

Parameters:

Name Type Description Default
skip_locked bool

Skip rows that are already locked (SKIP LOCKED)

 False
nowait bool

Return immediately if row is locked (NOWAIT)

 False
of str | list[str] | None

Table names/aliases to lock (FOR UPDATE OF table)

 None

Returns:

Type Description
Self

Self for method chaining

for_share 

for_share(*, skip_locked=False, nowait=False, of=None)

Add FOR SHARE clause for shared row-level locking.

Parameters:

Name Type Description Default
skip_locked bool

Skip rows that are already locked (SKIP LOCKED)

 False
nowait bool

Return immediately if row is locked (NOWAIT)

 False
of str | list[str] | None

Table names/aliases to lock (FOR SHARE OF table)

 None

Returns:

Type Description
Self

Self for method chaining

for_key_share 

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.

Returns:

Type Description
Self

Self for method chaining

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.

Returns:

Type Description
Self

Self for method chaining

Join, Window, Case & Subquery Builders

JoinBuilder

Builder for JOIN operations with fluent syntax.

on 

on(condition)

Set the join condition and build the JOIN expression.

Parameters:

Name Type Description Default
condition str | Expr

JOIN condition

 required

Returns:

Type Description
Expr

Complete JOIN expression

WindowFunctionBuilder

Helper to fluently construct window function expressions.

partition_by 

partition_by(*columns)

order_by 

order_by(*columns)

build 

build()

as_ 

as_(alias)

CaseBuilder

Fluent builder for CASE expressions used within SELECT clauses.

Case

Bases: ExpressionWrapper

Represent a SQL CASE expression with structured components.

SubqueryBuilder

Helper to build subquery expressions for EXISTS/IN/ANY/ALL operations.

Clause Mixins

SelectClauseMixin

Mixin providing SELECT clause methods.

WhereClauseMixin

OrderByClauseMixin

LimitOffsetClauseMixin

HavingClauseMixin

CommonTableExpressionMixin

Mixin for Common Table Expression (CTE) support.

CTE functionality is provided directly by :class:~sqlspec.builder._base.QueryBuilder. This class is retained for backward compatibility.

SetOperationMixin

PivotClauseMixin

UnpivotClauseMixin