0
# SQL Operations
1

2
SQLModel provides comprehensive SQL query building and execution capabilities, combining SQLAlchemy's power with enhanced type safety. This includes SELECT statement construction, SQL expression functions, and query execution utilities.
3

4
## Capabilities
5

6
### SELECT Statement Construction
7

8
Type-safe SELECT statement building with support for various query patterns.
9

10
```python { .api }
11
def select(*entities: Any) -> Union[Select, SelectOfScalar]:
12
    """
13
    Create a SELECT statement with proper typing based on selected entities.
14
    
15
    Parameters:
16
        *entities: Model classes, columns, or expressions to select
17
        
18
    Returns:
19
        Select object for multiple columns/entities
20
        SelectOfScalar object for single scalar values
21
    """
22

23
class Select(SelectBase[_T]):
24
    """
25
    Type-safe SELECT statement for multiple columns or entities.
26
    
27
    Provides methods for building complex queries with proper type inference.
28
    """
29

30
class SelectOfScalar(SelectBase[_T]):
31
    """
32
    SELECT statement for scalar (single value) results.
33
    
34
    Used when selecting single columns or aggregate functions.
35
    """
36
```
37

38
**Usage Examples:**
39
```python
40
# Select entire model
41
statement = select(Hero)  # Returns Select[Hero]
42
heroes = session.exec(statement).all()  # List[Hero]
43

44
# Select specific columns
45
statement = select(Hero.name, Hero.age)  # Returns Select[Tuple[str, Optional[int]]]
46
results = session.exec(statement).all()  # List[Tuple[str, Optional[int]]]
47

48
# Select scalar values
49
statement = select(Hero.name)  # Returns SelectOfScalar[str]
50
names = session.exec(statement).all()  # List[str]
51

52
# Aggregate functions
53
statement = select(func.count(Hero.id))  # Returns SelectOfScalar[int]
54
count = session.exec(statement).one()  # int
55

56
# Complex queries with joins
57
statement = select(Hero, Team).join(Team)
58
results = session.exec(statement).all()  # List[Tuple[Hero, Team]]
59
```
60

61
### Logical Operations
62

63
SQL logical operators for building WHERE clauses and complex conditions.
64

65
```python { .api }
66
def and_(*clauses) -> BooleanClauseList:
67
    """
68
    Create an AND expression from multiple conditions.
69
    
70
    Parameters:
71
        *clauses: Boolean expressions to combine with AND
72
        
73
    Returns:
74
        BooleanClauseList representing the AND operation
75
    """
76

77
def or_(*clauses) -> BooleanClauseList:
78
    """
79
    Create an OR expression from multiple conditions.
80
    
81
    Parameters:
82
        *clauses: Boolean expressions to combine with OR
83
        
84
    Returns:
85
        BooleanClauseList representing the OR operation
86
    """
87

88
def not_(clause) -> UnaryExpression:
89
    """
90
    Create a NOT expression to negate a condition.
91
    
92
    Parameters:
93
        clause: Boolean expression to negate
94
        
95
    Returns:
96
        UnaryExpression representing the NOT operation
97
    """
98
```
99

100
**Usage Examples:**
101
```python
102
# AND conditions
103
statement = select(Hero).where(
104
    and_(Hero.age > 18, Hero.name.contains("Spider"))
105
)
106

107
# OR conditions  
108
statement = select(Hero).where(
109
    or_(Hero.name == "Spider-Boy", Hero.name == "Spider-Man")
110
)
111

112
# NOT conditions
113
statement = select(Hero).where(
114
    not_(Hero.secret_name.contains("secret"))
115
)
116

117
# Complex combinations
118
statement = select(Hero).where(
119
    and_(
120
        Hero.age > 18,
121
        or_(Hero.name.contains("Spider"), Hero.name.contains("Super"))
122
    )
123
)
124
```
125

126
### Comparison and Range Operations
127

128
Functions for creating comparison and range-based conditions.
129

130
```python { .api }
131
def between(expr, lower_bound, upper_bound) -> BinaryExpression:
132
    """
133
    Create a BETWEEN expression for range queries.
134
    
135
    Parameters:
136
        expr: Column or expression to test
137
        lower_bound: Lower bound of the range
138
        upper_bound: Upper bound of the range
139
        
140
    Returns:
141
        BinaryExpression for the BETWEEN operation
142
    """
143

144
def all_(expr) -> CollectionAggregate[bool]:
145
    """
146
    Create an ALL expression for subquery comparisons.
147
    
148
    Parameters:
149
        expr: Expression or subquery to compare against
150
        
151
    Returns:
152
        CollectionAggregate for ALL comparison
153
    """
154

155
def any_(expr) -> CollectionAggregate[bool]:
156
    """
157
    Create an ANY expression for subquery comparisons.
158
    
159
    Parameters:
160
        expr: Expression or subquery to compare against
161
        
162
    Returns:
163
        CollectionAggregate for ANY comparison
164
    """
165
```
166

167
**Usage Examples:**
168
```python
169
# BETWEEN queries
170
statement = select(Hero).where(between(Hero.age, 18, 65))
171

172
# Subquery with ANY
173
subquery = select(Team.id).where(Team.name.contains("Avengers"))
174
statement = select(Hero).where(Hero.team_id.in_(subquery))
175

176
# Using ANY with arrays (PostgreSQL)
177
statement = select(Hero).where(Hero.id == any_([1, 2, 3, 4]))
178
```
179

180
### Ordering and Sorting
181

182
Functions for specifying query result ordering.
183

184
```python { .api }
185
def asc(column) -> UnaryExpression:
186
    """
187
    Create an ascending order expression.
188
    
189
    Parameters:
190
        column: Column or expression to sort by
191
        
192
    Returns:
193
        UnaryExpression for ascending order
194
    """
195

196
def desc(column) -> UnaryExpression:
197
    """
198
    Create a descending order expression.
199
    
200
    Parameters:
201
        column: Column or expression to sort by
202
        
203
    Returns:
204
        UnaryExpression for descending order
205
    """
206

207
def nulls_first(column) -> UnaryExpression:
208
    """
209
    Specify that NULL values should appear first in ordering.
210
    
211
    Parameters:
212
        column: Column expression to modify
213
        
214
    Returns:
215
        UnaryExpression with NULLS FIRST ordering
216
    """
217

218
def nulls_last(column) -> UnaryExpression:
219
    """
220
    Specify that NULL values should appear last in ordering.
221
    
222
    Parameters:
223
        column: Column expression to modify
224
        
225
    Returns:
226
        UnaryExpression with NULLS LAST ordering
227
    """
228
```
229

230
**Usage Examples:**
231
```python
232
# Basic ordering
233
statement = select(Hero).order_by(asc(Hero.name))
234
statement = select(Hero).order_by(desc(Hero.age))
235

236
# Multiple columns
237
statement = select(Hero).order_by(asc(Hero.team_id), desc(Hero.age))
238

239
# NULL handling
240
statement = select(Hero).order_by(nulls_last(asc(Hero.age)))
241
```
242

243
### Type Operations and Casting
244

245
Functions for type manipulation and casting in SQL expressions.
246

247
```python { .api }
248
def cast(expression, type_) -> Cast:
249
    """
250
    Create a CAST expression to convert types.
251
    
252
    Parameters:
253
        expression: Expression to cast
254
        type_: Target SQLAlchemy type
255
        
256
    Returns:
257
        Cast expression
258
    """
259

260
def type_coerce(expression, type_) -> TypeCoerce:
261
    """
262
    Coerce expression to a specific type for Python typing.
263
    
264
    Parameters:
265
        expression: Expression to coerce
266
        type_: Target SQLAlchemy type
267
        
268
    Returns:
269
        TypeCoerce expression
270
    """
271

272
def distinct(expr) -> UnaryExpression:
273
    """
274
    Create a DISTINCT expression.
275
    
276
    Parameters:
277
        expr: Expression to make distinct
278
        
279
    Returns:
280
        UnaryExpression for DISTINCT operation
281
    """
282
```
283

284
**Usage Examples:**
285
```python
286
# Type casting
287
statement = select(cast(Hero.age, String))
288

289
# Distinct values
290
statement = select(distinct(Hero.team_id))
291

292
# Type coercion for better typing
293
from sqlalchemy import JSON
294
json_data = type_coerce(Hero.metadata, JSON)
295
statement = select(json_data["key"])
296
```
297

298
### Conditional Expressions
299

300
Functions for creating conditional logic in SQL.
301

302
```python { .api }
303
def case(*whens, value=None, else_=None) -> Case:
304
    """
305
    Create a CASE expression for conditional logic.
306
    
307
    Parameters:
308
        *whens: Tuples of (condition, result) pairs
309
        value: Column to test against (for simple CASE)
310
        else_: Default value if no conditions match
311
        
312
    Returns:
313
        Case expression
314
    """
315
```
316

317
**Usage Examples:**
318
```python
319
# Conditional selection
320
hero_status = case(
321
    (Hero.age < 18, "Young"),
322
    (Hero.age < 65, "Adult"),
323
    else_="Senior"
324
)
325
statement = select(Hero.name, hero_status.label("status"))
326

327
# Simple CASE with value
328
team_type = case(
329
    ("Avengers", "Heroes"),
330
    ("X-Men", "Mutants"),
331
    value=Team.name,
332
    else_="Other"
333
)
334
```
335

336
### Aggregate and Window Functions
337

338
Support for aggregate functions and window operations.
339

340
```python { .api }
341
def over(element, partition_by=None, order_by=None) -> Over:
342
    """
343
    Create an OVER clause for window functions.
344
    
345
    Parameters:
346
        element: Function or expression to apply window to
347
        partition_by: Columns to partition by
348
        order_by: Columns to order by within partitions
349
        
350
    Returns:
351
        Over expression for window functions
352
    """
353

354
def within_group(element, *order_by) -> WithinGroup:
355
    """
356
    Create a WITHIN GROUP clause for ordered-set aggregates.
357
    
358
    Parameters:
359
        element: Aggregate function
360
        *order_by: Ordering expressions
361
        
362
    Returns:
363
        WithinGroup expression
364
    """
365
```
366

367
**Usage Examples:**
368
```python
369
# Window functions
370
from sqlalchemy import func
371

372
# Row numbers within partitions
373
row_num = func.row_number().over(
374
    partition_by=Hero.team_id,
375
    order_by=desc(Hero.age)
376
)
377
statement = select(Hero.name, row_num.label("rank"))
378

379
# Running totals
380
running_count = func.count().over(
381
    order_by=Hero.id,
382
    rows=(None, 0)  # Unbounded preceding to current row
383
)
384
```
385

386
### Column References and Utilities
387

388
Utilities for working with column references and expressions.
389

390
```python { .api }
391
def col(column_expression) -> Mapped:
392
    """
393
    Create a column reference with proper typing.
394
    
395
    Parameters:
396
        column_expression: Column to reference
397
        
398
    Returns:
399
        Mapped column reference
400
    """
401

402
def collate(expression, collation) -> BinaryExpression:
403
    """
404
    Apply a collation to a string expression.
405
    
406
    Parameters:
407
        expression: String expression
408
        collation: Collation name
409
        
410
    Returns:
411
        BinaryExpression with collation
412
    """
413

414
def extract(field, expr) -> Extract:
415
    """
416
    Extract a date/time component from a datetime expression.
417
    
418
    Parameters:
419
        field: Component to extract ("year", "month", "day", etc.)
420
        expr: Datetime expression
421
        
422
    Returns:
423
        Extract expression
424
    """
425
```
426

427
**Usage Examples:**
428
```python
429
# Column references
430
hero_name = col(Hero.name)
431
statement = select(hero_name).where(hero_name.like("Spider%"))
432

433
# Date/time extraction
434
year_born = extract("year", Hero.birth_date)
435
statement = select(Hero.name, year_born.label("birth_year"))
436

437
# String collation
438
statement = select(Hero).where(
439
    collate(Hero.name, "NOCASE") == "spider-man"
440
)
441
```