Skip to content

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

Builder for JOIN operations with fluent syntax.

as_of(time_expr, kind=None)

Set AS OF clause for the join (Time Travel/Flashback).

on(condition)

Set the join condition and build the JOIN expression.

WindowFunctionBuilder

Helper to fluently construct window function expressions.

partition_by(*columns)

order_by(*columns)

build()

as_(alias)

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
when(condition, result)
else_(value)
end()

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.

Clause Mixins

SelectClauseMixin

get_expression()
set_expression(expression)
select(*columns)
select_only(*columns)
distinct(*columns)
from_(table, alias=None, as_of=None, as_of_type=None)
group_by(*columns)
group_by_rollup(*columns)
group_by_cube(*columns)
group_by_grouping_sets(*column_sets)

WhereClauseMixin

get_expression
set_expression
where
where_eq
where_neq
where_lt
where_lte
where_gt
where_gte
where_between
where_like
where_not_like
where_ilike
where_is_null
where_is_not_null
where_in
where_not_in
where_any
where_not_any
where_exists
where_not_exists
where_like_any
or_where_eq
or_where_neq
or_where_lt
or_where_lte
or_where_gt
or_where_gte
or_where_between
or_where_like
or_where_not_like
or_where_ilike
or_where_is_null
or_where_is_not_null
or_where_null
or_where_not_null
or_where_in
or_where_not_in
or_where_any
or_where_not_any
or_where_exists
or_where_not_exists
where_or
or_where

JoinClauseMixin

join
inner_join
left_join
right_join
full_join
cross_join
lateral_join
left_lateral_join
cross_lateral_join

OrderByClauseMixin

order_by(*items, desc=False)

LimitOffsetClauseMixin

limit(value)
approx_limit(value, *, target_accuracy=None)
offset(value)

HavingClauseMixin

having(condition)

CommonTableExpressionMixin

SetOperationMixin

dialect = None
get_expression()
set_expression(expression)
set_parameters(parameters)
union(other, all_=False)
intersect(other)
except_(other)

PivotClauseMixin

pivot(aggregate_function, aggregate_column, pivot_column, pivot_values, alias=None)

UnpivotClauseMixin

unpivot(value_column_name, name_column_name, columns_to_unpivot, alias=None)