0
# Terms and Expressions
1

2
Building blocks for SQL expressions including fields, criteria, case statements, arrays, tuples, and custom functions. These components support all SQL operations and can be combined to create complex expressions with full operator support and type safety.
3

4
## Capabilities
5

6
### Field Operations
7

8
Field references supporting all SQL operations, comparisons, and transformations.
9

10
```python { .api }
11
class Field:
12
    def __init__(self, name: str, alias: Optional[str] = None, table: Optional[Table] = None):
13
        """
14
        Create field reference.
15
        
16
        Parameters:
17
        - name: Field name
18
        - alias: Field alias
19
        - table: Parent table
20
        """
21
        
22
    def as_(self, alias: str) -> Field:
23
        """Set field alias."""
24
        
25
    def asc(self) -> Order:
26
        """Create ascending order."""
27
        
28
    def desc(self) -> Order:
29
        """Create descending order."""
30
        
31
    def isnull(self) -> BasicCriterion:
32
        """Check if field is NULL."""
33
        
34
    def notnull(self) -> BasicCriterion:
35
        """Check if field is NOT NULL."""
36
        
37
    def isin(self, *values) -> BasicCriterion:
38
        """Check if field IN values."""
39
        
40
    def notin(self, *values) -> BasicCriterion:
41
        """Check if field NOT IN values."""
42
        
43
    def between(self, lower, upper) -> RangeCriterion:
44
        """Check if field BETWEEN lower and upper."""
45
        
46
    def like(self, pattern) -> BasicCriterion:
47
        """Pattern matching with LIKE."""
48
        
49
    def not_like(self, pattern) -> BasicCriterion:
50
        """Pattern matching with NOT LIKE."""
51
        
52
    def rlike(self, pattern) -> BasicCriterion:
53
        """Regular expression matching with RLIKE."""
54
        
55
    def not_rlike(self, pattern) -> BasicCriterion:
56
        """Regular expression matching with NOT RLIKE."""
57
        
58
    def distinct(self) -> DistinctOptionField:
59
        """Mark field as DISTINCT."""
60
        
61
    # Arithmetic operations
62
    def __add__(self, other):
63
        """Addition: field + other"""
64
        
65
    def __sub__(self, other):
66
        """Subtraction: field - other"""
67
        
68
    def __mul__(self, other):
69
        """Multiplication: field * other"""
70
        
71
    def __truediv__(self, other):
72
        """Division: field / other"""
73
        
74
    def __mod__(self, other):
75
        """Modulo: field % other"""
76
        
77
    # Comparison operations
78
    def __eq__(self, other):
79
        """Equality: field == other"""
80
        
81
    def __ne__(self, other):
82
        """Inequality: field != other"""
83
        
84
    def __lt__(self, other):
85
        """Less than: field < other"""
86
        
87
    def __le__(self, other):
88
        """Less than or equal: field <= other"""
89
        
90
    def __gt__(self, other):
91
        """Greater than: field > other"""
92
        
93
    def __ge__(self, other):
94
        """Greater than or equal: field >= other"""
95
```
96

97
**Usage Examples:**
98

99
```python
100
from pypika import Table, Query, Field
101

102
users = Table('users')
103
orders = Table('orders')
104

105
# Field comparisons
106
adult_users = users.age >= 18
107
active_users = users.status == 'active'
108
recent_orders = orders.created_at > '2023-01-01'
109

110
# Null checks
111
users_with_email = users.email.notnull()
112
users_without_phone = users.phone.isnull()
113

114
# IN operations
115
vip_statuses = users.status.isin('gold', 'platinum', 'diamond')
116
excluded_ids = users.id.notin(1, 2, 3)
117

118
# Pattern matching
119
gmail_users = users.email.like('%@gmail.com')
120
non_temp_users = users.email.not_like('temp_%')
121

122
# BETWEEN operations
123
middle_aged = users.age.between(25, 65)
124
recent_range = orders.created_at.between('2023-01-01', '2023-12-31')
125

126
# Arithmetic operations
127
total_with_tax = orders.subtotal + orders.tax
128
discount_percent = (orders.discount / orders.subtotal) * 100
129
monthly_salary = users.annual_salary / 12
130

131
# Field aliasing
132
full_name = (users.first_name + ' ' + users.last_name).as_('full_name')
133
age_group = Field(
134
    Case()
135
    .when(users.age < 18, 'Minor')
136
    .when(users.age < 65, 'Adult')
137
    .else_('Senior')
138
).as_('age_group')
139

140
# Ordering
141
query = Query.from_(users).select('*').orderby(users.created_at.desc(), users.name.asc())
142

143
# DISTINCT fields
144
query = Query.from_(users).select(users.status.distinct())
145
```
146

147
### Criterion Operations
148

149
Boolean expressions for WHERE clauses with logical operations and complex condition building.
150

151
```python { .api }
152
class Criterion:
153
    @staticmethod
154
    def any(*terms) -> EmptyCriterion:
155
        """Create OR-combined criterion from multiple terms."""
156
        
157
    @staticmethod
158
    def all(*terms) -> EmptyCriterion:
159
        """Create AND-combined criterion from multiple terms."""
160
        
161
    # Logical operations
162
    def __and__(self, other):
163
        """Logical AND: criterion & other"""
164
        
165
    def __or__(self, other):
166
        """Logical OR: criterion | other"""
167
        
168
    def __invert__(self):
169
        """Logical NOT: ~criterion"""
170

171
class EmptyCriterion(Criterion):
172
    """Neutral element for criterion combinations."""
173
```
174

175
**Usage Examples:**
176

177
```python
178
from pypika import Table, Query, Criterion
179

180
users = Table('users')
181
orders = Table('orders')
182

183
# Simple conditions
184
adult_criterion = users.age >= 18
185
active_criterion = users.status == 'active'
186

187
# Combining conditions with logical operators
188
adult_active = adult_criterion & active_criterion
189
inactive_or_young = ~active_criterion | (users.age < 18)
190

191
# Complex condition building
192
premium_users = (
193
    (users.status.isin('gold', 'platinum')) &
194
    (users.total_orders > 10) &
195
    (users.last_login > '2023-01-01')
196
)
197

198
# Using Criterion.all() and Criterion.any()
199
all_conditions = Criterion.all([
200
    users.age >= 18,
201
    users.status == 'active',
202
    users.email.notnull()
203
])
204

205
any_conditions = Criterion.any([
206
    users.status == 'premium',
207
    users.total_spent > 1000,
208
    users.referral_count > 5
209
])
210

211
# Nested conditions
212
complex_criterion = (
213
    Criterion.all([
214
        users.status == 'active',
215
        users.age >= 18
216
    ]) |
217
    Criterion.any([
218
        users.is_vip == True,
219
        users.total_spent > 5000
220
    ])
221
)
222

223
# Using in queries
224
query = Query.from_(users).select('*').where(complex_criterion)
225
```
226

227
### Case Expressions
228

229
SQL CASE statement builder for conditional logic and value transformation.
230

231
```python { .api }
232
class Case:
233
    def when(self, criterion: Criterion, term) -> Case:
234
        """
235
        Add WHEN clause.
236
        
237
        Parameters:
238
        - criterion: Condition to check
239
        - term: Value to return if condition is true
240
        """
241
        
242
    def else_(self, term) -> Case:
243
        """
244
        Add ELSE clause.
245
        
246
        Parameters:
247
        - term: Default value to return
248
        """
249
```
250

251
**Usage Examples:**
252

253
```python
254
from pypika import Table, Query, Case
255

256
users = Table('users')
257
orders = Table('orders')
258

259
# Simple CASE expression
260
status_description = (
261
    Case()
262
    .when(users.status == 'active', 'User is active')
263
    .when(users.status == 'inactive', 'User is inactive')
264
    .else_('Unknown status')
265
).as_('status_description')
266

267
# Numeric CASE
268
age_category = (
269
    Case()
270
    .when(users.age < 18, 'Minor')
271
    .when(users.age < 30, 'Young Adult')
272
    .when(users.age < 50, 'Adult')
273
    .when(users.age < 65, 'Middle Aged')
274
    .else_('Senior')
275
).as_('age_category')
276

277
# Complex conditions in CASE
278
user_tier = (
279
    Case()
280
    .when((users.total_spent > 10000) & (users.years_active > 5), 'Platinum')
281
    .when(users.total_spent > 5000, 'Gold')
282
    .when(users.total_spent > 1000, 'Silver')
283
    .else_('Bronze')
284
).as_('user_tier')
285

286
# CASE in SELECT
287
query = Query.from_(users).select(
288
    users.name,
289
    users.email,
290
    status_description,
291
    age_category,
292
    user_tier
293
)
294

295
# CASE in WHERE clause
296
query = Query.from_(users).select('*').where(
297
    Case()
298
    .when(users.role == 'admin', users.last_login > '2023-01-01')
299
    .else_(users.last_login > '2023-06-01')
300
)
301

302
# CASE in ORDER BY
303
query = Query.from_(users).select('*').orderby(
304
    Case()
305
    .when(users.status == 'active', 1)
306
    .when(users.status == 'pending', 2)
307
    .else_(3)
308
)
309
```
310

311
### Array and Tuple Operations
312

313
SQL array and tuple literal support for complex data structures.
314

315
```python { .api }
316
class Array:
317
    def __init__(self, *items):
318
        """
319
        Create SQL array literal.
320
        
321
        Parameters:
322
        - items: Array elements
323
        """
324

325
class Tuple:
326
    def __init__(self, *items):
327
        """
328
        Create SQL tuple literal.
329
        
330
        Parameters:
331
        - items: Tuple elements
332
        """
333
```
334

335
**Usage Examples:**
336

337
```python
338
from pypika import Table, Query, Array, Tuple
339

340
users = Table('users')
341
orders = Table('orders')
342

343
# Array operations
344
user_roles = Array('admin', 'user', 'moderator')
345
status_array = Array('active', 'pending', 'inactive')
346

347
# Using arrays in queries
348
query = Query.from_(users).select('*').where(
349
    users.role.isin(user_roles)
350
)
351

352
# Tuple operations for multi-column conditions
353
user_tuple = Tuple(users.first_name, users.last_name)
354
name_combinations = Tuple(
355
    Tuple('John', 'Doe'),
356
    Tuple('Jane', 'Smith'),
357
    Tuple('Bob', 'Johnson')
358
)
359

360
query = Query.from_(users).select('*').where(
361
    user_tuple.isin(name_combinations)
362
)
363

364
# Arrays in PostgreSQL-specific operations (when using PostgreSQLQuery)
365
from pypika import PostgreSQLQuery
366
pg_users = Table('users', query_cls=PostgreSQLQuery)
367

368
# Array contains operations (PostgreSQL specific)
369
tags_array = Array('python', 'sql', 'data')
370
# Note: Specific array operations depend on dialect support
371
```
372

373
### JSON Operations
374

375
JSON field operations and path-based access for PostgreSQL-style JSON handling.
376

377
```python { .api }
378
class JSON:
379
    def get_json_value(self, key_or_index) -> BasicCriterion:
380
        """JSON -> operator: extract JSON value."""
381
        
382
    def get_text_value(self, key_or_index) -> BasicCriterion:
383
        """JSON ->> operator: extract JSON value as text."""
384
        
385
    def get_path_json_value(self, path_json) -> BasicCriterion:
386
        """JSON #> operator: extract JSON value at path."""
387
        
388
    def get_path_text_value(self, path_json) -> BasicCriterion:
389
        """JSON #>> operator: extract JSON value at path as text."""
390
        
391
    def has_key(self, key) -> BasicCriterion:
392
        """JSON ? operator: check if key exists."""
393
        
394
    def contains(self, other) -> BasicCriterion:
395
        """JSON @> operator: check if contains value."""
396
        
397
    def contained_by(self, other) -> BasicCriterion:
398
        """JSON <@ operator: check if contained by value."""
399
        
400
    def has_keys(self, keys) -> BasicCriterion:
401
        """JSON ?& operator: check if has all keys."""
402
        
403
    def has_any_keys(self, keys) -> BasicCriterion:
404
        """JSON ?| operator: check if has any keys."""
405
```
406

407
**Usage Examples:**
408

409
```python
410
from pypika import Table, Query, JSON, PostgreSQLQuery
411

412
# Table with JSON fields (typically PostgreSQL)
413
users = Table('users', query_cls=PostgreSQLQuery)
414

415
# JSON field operations
416
profile = users.profile  # Assuming 'profile' is a JSON field
417

418
# Extract JSON values
419
name = profile.get_json_value('name')
420
age = profile.get_text_value('age')
421

422
# Path-based extraction
423
address_city = profile.get_path_text_value(['address', 'city'])
424
phone_numbers = profile.get_path_json_value(['contact', 'phones'])
425

426
# Key existence checks
427
has_email = profile.has_key('email')
428
has_address = profile.has_key('address')
429

430
# Multiple key checks
431
has_contact_info = profile.has_keys(['email', 'phone'])
432
has_any_social = profile.has_any_keys(['twitter', 'facebook', 'linkedin'])
433

434
# Contains operations
435
has_premium = profile.contains({'subscription': 'premium'})
436
is_admin = profile.contained_by({'roles': ['admin', 'user']})
437

438
# Using in queries
439
query = Query.from_(users).select(
440
    users.id,
441
    profile.get_text_value('name').as_('profile_name'),
442
    profile.get_path_text_value(['address', 'city']).as_('city')
443
).where(
444
    profile.has_key('email') & 
445
    profile.get_text_value('status').eq('active')
446
)
447
```
448

449
### Custom Functions
450

451
Factory for creating user-defined SQL functions with flexible parameter handling.
452

453
```python { .api }
454
class CustomFunction:
455
    def __init__(self, name: str, params: Optional[list] = None):
456
        """
457
        Create custom SQL function.
458
        
459
        Parameters:
460
        - name: Function name
461
        - params: Parameter specifications
462
        """
463
        
464
    def __call__(self, *args) -> Function:
465
        """Call function with arguments."""
466
```
467

468
**Usage Examples:**
469

470
```python
471
from pypika import Table, Query, CustomFunction
472

473
users = Table('users')
474

475
# Define custom functions
476
calculate_age = CustomFunction('CALCULATE_AGE')
477
hash_email = CustomFunction('HASH_EMAIL')
478
format_phone = CustomFunction('FORMAT_PHONE')
479
distance_between = CustomFunction('DISTANCE_BETWEEN')
480

481
# Use custom functions in queries
482
query = Query.from_(users).select(
483
    users.name,
484
    calculate_age(users.birth_date).as_('age'),
485
    hash_email(users.email).as_('email_hash'),
486
    format_phone(users.phone).as_('formatted_phone')
487
)
488

489
# Custom aggregation functions
490
custom_avg = CustomFunction('CUSTOM_AVG')
491
custom_count = CustomFunction('COUNT_DISTINCT_VALUES')
492

493
query = Query.from_(users).select(
494
    custom_avg(users.score).as_('avg_score'),
495
    custom_count(users.category).as_('unique_categories')
496
).groupby(users.department)
497

498
# Custom window functions
499
custom_rank = CustomFunction('CUSTOM_RANK')
500
custom_lag = CustomFunction('PREV_VALUE')
501

502
query = Query.from_(users).select(
503
    users.name,
504
    users.salary,
505
    custom_rank().over(users.department).orderby(users.salary.desc()).as_('dept_rank'),
506
    custom_lag(users.salary, 1).over(users.department).orderby(users.hire_date).as_('prev_salary')
507
)
508

509
# Geographic/spatial functions
510
st_distance = CustomFunction('ST_DISTANCE')
511
st_within = CustomFunction('ST_WITHIN')
512

513
locations = Table('locations')
514
query = Query.from_(locations).select(
515
    locations.name,
516
    st_distance(locations.point, 'POINT(0 0)').as_('distance_from_origin')
517
).where(
518
    st_within(locations.point, 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
519
)
520
```
521

522
### Special Values and Constants
523

524
Built-in constants and special value representations.
525

526
```python { .api }
527
class NullValue:
528
    """SQL NULL value."""
529

530
class SystemTimeValue:
531
    """SQL SYSTEM_TIME value for temporal tables."""
532

533
class Not:
534
    def __init__(self, term):
535
        """NOT operator wrapper."""
536

537
class Bracket:
538
    def __init__(self, term):
539
        """Parentheses wrapper for expressions."""
540

541
class Index:
542
    def __init__(self, name: str):
543
        """Database index reference."""
544
```
545

546
**Usage Examples:**
547

548
```python
549
from pypika import Table, Query, NULL, SYSTEM_TIME, Not, Bracket, Index
550

551
users = Table('users')
552

553
# NULL operations
554
query = Query.from_(users).select('*').where(users.deleted_at == NULL)
555
query = Query.from_(users).select('*').where(users.phone != NULL)
556

557
# NOT operations
558
not_admin = Not(users.role == 'admin')
559
query = Query.from_(users).select('*').where(not_admin)
560

561
# Explicit parentheses
562
complex_condition = Bracket(
563
    (users.age > 18) & (users.status == 'active')
564
) | Bracket(
565
    (users.role == 'admin') & (users.verified == True)
566
)
567

568
query = Query.from_(users).select('*').where(complex_condition)
569

570
# Index hints (MySQL specific)
571
user_email_idx = Index('idx_user_email')
572
# Usage depends on dialect-specific implementation
573

574
# SYSTEM_TIME for temporal tables
575
historical_query = Query.from_(users.for_(SYSTEM_TIME.between('2023-01-01', '2023-12-31')))
576
```
577

578
### Interval Operations
579

580
Time and date interval expressions for temporal calculations.
581

582
```python { .api }
583
class Interval:
584
    def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, 
585
                 seconds=0, microseconds=0, quarters=0, weeks=0, dialect=None):
586
        """
587
        Create time interval.
588
        
589
        Parameters:
590
        - years, months, days, hours, minutes, seconds, microseconds: Time components
591
        - quarters, weeks: Additional time units
592
        - dialect: Database dialect for interval formatting
593
        """
594
```
595

596
**Usage Examples:**
597

598
```python
599
from pypika import Table, Query, Interval
600
from pypika.functions import Now
601

602
users = Table('users')
603
orders = Table('orders')
604

605
# Date arithmetic with intervals
606
one_year_ago = Now() - Interval(years=1)
607
thirty_days_ago = Now() - Interval(days=30)
608
six_months = Interval(months=6)
609

610
# Recent users (last 30 days)
611
query = Query.from_(users).select('*').where(
612
    users.created_at > thirty_days_ago
613
)
614

615
# Users created more than a year ago
616
query = Query.from_(users).select('*').where(
617
    users.created_at < one_year_ago
618
)
619

620
# Calculate future dates
621
renewal_date = users.subscription_start + Interval(years=1)
622
trial_end = users.trial_start + Interval(days=14)
623

624
query = Query.from_(users).select(
625
    users.name,
626
    users.subscription_start,
627
    renewal_date.as_('renewal_date'),
628
    trial_end.as_('trial_end')
629
)
630

631
# Complex interval calculations
632
quarterly_review = users.hire_date + Interval(quarters=1)
633
annual_bonus_date = users.hire_date + Interval(years=1, days=-1)
634

635
# Filter by interval ranges
636
recent_orders = Query.from_(orders).select('*').where(
637
    orders.created_at.between(
638
        Now() - Interval(days=7),
639
        Now()
640
    )
641
)
642
```