0
# Expression Building and AST Manipulation
1

2
Programmatic SQL construction using builder functions and direct AST manipulation. Build complex SQL queries through code, modify existing parsed queries, and traverse expression trees for analysis and transformation.
3

4
## Capabilities
5

6
### Core Expression Builders
7

8
Essential functions for constructing SQL expressions programmatically.
9

10
```python { .api }
11
def select(*expressions) -> Select:
12
    """
13
    Creates a SELECT expression with the specified columns/expressions.
14
    
15
    Args:
16
        *expressions: Column names, expressions, or Expression objects to select
17
        
18
    Returns:
19
        Select: A SELECT expression object
20
    """
21

22
def column(col: str) -> Column:
23
    """
24
    Creates a column reference expression.
25
    
26
    Args:
27
        col (str): Column name, optionally qualified (e.g., "table.column")
28
        
29
    Returns:
30
        Column: Column reference expression
31
    """
32

33
def table_(name: str) -> Table:
34
    """
35
    Creates a table reference expression.
36
    
37
    Args:
38
        name (str): Table name, optionally qualified (e.g., "schema.table")
39
        
40
    Returns:
41
        Table: Table reference expression
42
    """
43

44
def func(name: str, *args) -> Function:
45
    """
46
    Creates a function call expression.
47
    
48
    Args:
49
        name (str): Function name (e.g., "COUNT", "SUM", "MAX")
50
        *args: Function arguments as strings or Expression objects
51
        
52
    Returns:
53
        Function: Function call expression
54
    """
55
```
56

57
### Logical Operators
58

59
Build complex logical conditions and boolean expressions.
60

61
```python { .api }
62
def and_(*conditions) -> And:
63
    """
64
    Creates an AND logical expression combining multiple conditions.
65
    
66
    Args:
67
        *conditions: Boolean expressions to combine with AND
68
        
69
    Returns:
70
        And: AND logical expression
71
    """
72

73
def or_(*conditions) -> Or:
74
    """
75
    Creates an OR logical expression combining multiple conditions.
76
    
77
    Args:
78
        *conditions: Boolean expressions to combine with OR
79
        
80
    Returns:
81
        Or: OR logical expression
82
    """
83

84
def not_(condition) -> Not:
85
    """
86
    Creates a NOT logical expression negating a condition.
87
    
88
    Args:
89
        condition: Boolean expression to negate
90
        
91
    Returns:
92
        Not: NOT logical expression
93
    """
94
```
95

96
### Advanced Expression Builders
97

98
More complex SQL constructs for sophisticated query building.
99

100
```python { .api }
101
def case() -> Case:
102
    """
103
    Creates a CASE expression for conditional logic.
104
    
105
    Returns:
106
        Case: CASE expression object with .when() and .else_() methods
107
    """
108

109
def cast(expression, to: str) -> Cast:
110
    """
111
    Creates a CAST expression for type conversion.
112
    
113
    Args:
114
        expression: Expression or value to cast
115
        to (str): Target data type
116
        
117
    Returns:
118
        Cast: CAST expression
119
    """
120

121
def alias(expression, alias: str) -> Alias:
122
    """
123
    Creates an alias expression (AS clause).
124
    
125
    Args:
126
        expression: Expression to alias
127
        alias (str): Alias name
128
        
129
    Returns:
130
        Alias: Alias expression
131
    """
132

133
def subquery(query: Expression) -> Select:
134
    """
135
    Creates a subquery expression wrapped in parentheses.
136
    
137
    Args:
138
        query (Expression): SELECT or other query expression
139
        
140
    Returns:
141
        Select: Subquery expression
142
    """
143
```
144

145
### Statement Builders
146

147
Build complete SQL statements beyond SELECT queries.
148

149
```python { .api }
150
def insert() -> Insert:
151
    """
152
    Creates an INSERT statement expression.
153
    
154
    Returns:
155
        Insert: INSERT statement with .into() and .values() methods
156
    """
157

158
def delete() -> Delete:
159
    """
160
    Creates a DELETE statement expression.
161
    
162
    Returns:
163
        Delete: DELETE statement with .from_() and .where() methods
164
    """
165

166
def merge() -> Merge:
167
    """
168
    Creates a MERGE statement expression for upsert operations.
169
    
170
    Returns:
171
        Merge: MERGE statement expression
172
    """
173
```
174

175
### Set Operations
176

177
Combine query results using set operations.
178

179
```python { .api }
180
def union(*queries) -> Union:
181
    """
182
    Creates a UNION expression combining multiple queries.
183
    
184
    Args:
185
        *queries: SELECT expressions to union
186
        
187
    Returns:
188
        Union: UNION expression
189
    """
190

191
def intersect(*queries) -> Intersect:
192
    """
193
    Creates an INTERSECT expression.
194
    
195
    Args:
196
        *queries: SELECT expressions to intersect
197
        
198
    Returns:
199
        Intersect: INTERSECT expression
200
    """
201

202
def except_(*queries) -> Except:
203
    """
204
    Creates an EXCEPT expression.
205
    
206
    Args:
207
        *queries: SELECT expressions for set difference
208
        
209
    Returns:
210
        Except: EXCEPT expression
211
    """
212
```
213

214
### Utility Functions
215

216
Helper functions for common expression operations.
217

218
```python { .api }
219
def condition(expression) -> Expression:
220
    """
221
    Converts an expression into a boolean condition.
222
    
223
    Args:
224
        expression: Expression to convert to condition
225
        
226
    Returns:
227
        Expression: Boolean condition expression
228
    """
229

230
def maybe_parse(sql: str | Expression, **opts) -> Expression:
231
    """
232
    Parses SQL string or returns Expression if already parsed.
233
    
234
    Args:
235
        sql: SQL string or Expression object
236
        **opts: Parse options if parsing needed
237
        
238
    Returns:
239
        Expression: Parsed or existing expression
240
    """
241

242
def to_column(col: str | Expression) -> Column:
243
    """
244
    Converts string or expression to Column reference.
245
    
246
    Args:
247
        col: Column name or expression
248
        
249
    Returns:
250
        Column: Column reference expression
251
    """
252

253
def to_table(table: str | Expression) -> Table:
254
    """
255
    Converts string or expression to Table reference.
256
    
257
    Args:
258
        table: Table name or expression
259
        
260
    Returns:
261
        Table: Table reference expression
262
    """
263

264
def to_identifier(name: str | Expression) -> Identifier:
265
    """
266
    Converts string to Identifier expression.
267
    
268
    Args:
269
        name: Identifier name
270
        
271
    Returns:
272
        Identifier: Identifier expression
273
    """
274

275
def from_(*tables) -> From:
276
    """
277
    Creates a FROM clause expression.
278
    
279
    Args:
280
        *tables: Table names or expressions
281
        
282
    Returns:
283
        From: FROM clause expression
284
    """
285

286
def update() -> Update:
287
    """
288
    Creates an UPDATE statement expression.
289
    
290
    Returns:
291
        Update: UPDATE statement with .set() and .where() methods
292
    """
293
```
294

295
## Usage Examples
296

297
### Building Complex Queries
298

299
```python
300
import sqlglot
301
from sqlglot import select, column, table_, func, and_, or_, case
302

303
# Build a complex analytical query
304
query = (
305
    select(
306
        column("user_id"),
307
        func("COUNT", "*").as_("total_orders"),
308
        func("SUM", column("amount")).as_("total_spent"),
309
        case()
310
            .when(func("SUM", column("amount")) > 1000, "VIP")
311
            .when(func("SUM", column("amount")) > 500, "Premium")
312
            .else_("Standard")
313
            .as_("customer_tier")
314
    )
315
    .from_(table_("orders"))
316
    .join(table_("users"), on=column("orders.user_id") == column("users.id"))
317
    .where(
318
        and_(
319
            column("order_date") >= "2023-01-01",
320
            column("status") == "completed"
321
        )
322
    )
323
    .group_by(column("user_id"))
324
    .having(func("COUNT", "*") > 5)
325
    .order_by(func("SUM", column("amount")).desc())
326
)
327

328
print(query.sql())
329
```
330

331
### Modifying Existing Queries
332

333
```python
334
import sqlglot
335

336
# Parse existing query
337
original = sqlglot.parse_one("SELECT name, age FROM users WHERE age > 25")
338

339
# Add additional columns
340
original = original.select("email", "phone", append=True)
341

342
# Modify WHERE condition  
343
original = original.where(
344
    and_(
345
        column("age") > 25,
346
        column("status") == "active"
347
    ),
348
    copy=False
349
)
350

351
# Add ORDER BY
352
original = original.order_by("name")
353

354
print(original.sql())
355
```
356

357
### Working with Expressions
358

359
```python
360
import sqlglot
361
from sqlglot import column, func, and_, or_
362

363
# Build complex conditions
364
condition1 = column("age") > 18
365
condition2 = column("status") == "active"
366
condition3 = column("last_login") > "2023-01-01"
367

368
# Combine conditions
369
complex_condition = and_(
370
    condition1,
371
    or_(condition2, condition3)
372
)
373

374
# Use in query
375
query = (
376
    sqlglot.select("*")
377
    .from_("users")
378
    .where(complex_condition)
379
)
380

381
print(query.sql())
382
# SELECT * FROM users WHERE age > 18 AND (status = 'active' OR last_login > '2023-01-01')
383
```
384

385
### Function Calls and Aggregations
386

387
```python
388
import sqlglot
389
from sqlglot import select, func, column
390

391
# Common aggregation functions
392
query = select(
393
    func("COUNT", "*").as_("total_rows"),
394
    func("AVG", column("price")).as_("avg_price"),
395
    func("MAX", column("created_date")).as_("latest_date"),
396
    func("STRING_AGG", column("name"), ", ").as_("all_names")
397
).from_("products")
398

399
# Window functions (using raw SQL for now)
400
windowed_query = sqlglot.parse_one("""
401
    SELECT 
402
        name,
403
        salary,
404
        ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
405
    FROM employees
406
""")
407

408
print(query.sql())
409
print(windowed_query.sql())
410
```
411

412
## Types
413

414
```python { .api }
415
class Expression:
416
    """Base class for all SQL expressions with manipulation methods."""
417
    
418
    def sql(self, dialect: str = None, **opts) -> str:
419
        """Generate SQL string from expression."""
420
    
421
    def transform(self, fun: Callable, **opts) -> Expression:
422
        """Apply transformation function to expression tree."""
423
    
424
    def find(self, expression_type: Type, **opts) -> Optional[Expression]:
425
        """Find first occurrence of expression type in tree."""
426
    
427
    def find_all(self, expression_type: Type, **opts) -> List[Expression]:
428
        """Find all occurrences of expression type in tree."""
429
    
430
    def replace(self, expression: Expression, **opts) -> Expression:
431
        """Replace this expression with another."""
432

433
class Select(Expression):
434
    """SELECT statement expression with builder methods."""
435
    
436
    def select(self, *expressions, append: bool = False) -> Select:
437
        """Add columns to SELECT clause."""
438
    
439
    def from_(self, *tables) -> Select:
440
        """Set FROM clause."""
441
    
442
    def where(self, condition, copy: bool = True) -> Select:
443
        """Add WHERE condition."""
444
    
445
    def group_by(self, *expressions) -> Select:
446
        """Add GROUP BY clause."""
447
    
448
    def having(self, condition) -> Select:
449
        """Add HAVING condition."""
450
    
451
    def order_by(self, *expressions) -> Select:
452
        """Add ORDER BY clause."""
453
    
454
    def limit(self, count: int) -> Select:
455
        """Add LIMIT clause."""
456
    
457
    def join(self, table, on: Expression = None, join_type: str = "INNER") -> Select:
458
        """Add JOIN clause."""
459

460
class Column(Expression):
461
    """Column reference expression."""
462
    
463
    def __eq__(self, other) -> Binary:
464
        """Create equality comparison."""
465
    
466
    def __gt__(self, other) -> Binary:
467
        """Create greater than comparison."""
468
    
469
    def __lt__(self, other) -> Binary:
470
        """Create less than comparison."""
471
    
472
    def desc(self) -> Ordered:
473
        """Create descending order expression."""
474
    
475
    def asc(self) -> Ordered:
476
        """Create ascending order expression."""
477

478
class Function(Expression):
479
    """Function call expression."""
480
    
481
    def as_(self, alias: str) -> Alias:
482
        """Create alias for function result."""
483

484
class Case(Expression):
485
    """CASE expression for conditional logic."""
486
    
487
    def when(self, condition, then_value) -> Case:
488
        """Add WHEN clause to CASE expression."""
489
    
490
    def else_(self, value) -> Case:
491
        """Add ELSE clause to CASE expression."""
492
```