0
# Core Query Operations
1

2
Primary interface for constructing SQL queries including SELECT, INSERT, UPDATE, DELETE, and DDL operations. The Query class provides static factory methods for all query types with fluent method chaining and immutable builder pattern.
3

4
## Capabilities
5

6
### SELECT Queries
7

8
Build SELECT statements with comprehensive support for joins, subqueries, aggregation, ordering, and grouping.
9

10
```python { .api }
11
class Query:
12
    @staticmethod
13
    def from_(table) -> QueryBuilder:
14
        """Initialize SELECT query from a table."""
15
        
16
    @staticmethod
17
    def select(*terms) -> QueryBuilder:
18
        """Initialize SELECT query without specifying table (for subqueries)."""
19

20
class QueryBuilder:
21
    def select(self, *terms) -> QueryBuilder:
22
        """Specify columns to select."""
23
        
24
    def where(self, criterion) -> QueryBuilder:
25
        """Add WHERE conditions."""
26
        
27
    def join(self, table, how=JoinType.inner) -> JoiningQueryBuilder:
28
        """Add JOIN clause."""
29
        
30
    def left_join(self, table) -> JoiningQueryBuilder:
31
        """Add LEFT JOIN clause."""
32
        
33
    def right_join(self, table) -> JoiningQueryBuilder:
34
        """Add RIGHT JOIN clause."""
35
        
36
    def inner_join(self, table) -> JoiningQueryBuilder:
37
        """Add INNER JOIN clause."""
38
        
39
    def outer_join(self, table) -> JoiningQueryBuilder:
40
        """Add OUTER JOIN clause."""
41
        
42
    def cross_join(self, table) -> QueryBuilder:
43
        """Add CROSS JOIN clause."""
44
        
45
    def groupby(self, *terms) -> QueryBuilder:
46
        """Add GROUP BY clause."""
47
        
48
    def having(self, criterion) -> QueryBuilder:
49
        """Add HAVING clause."""
50
        
51
    def orderby(self, *terms, order=Order.asc) -> QueryBuilder:
52
        """Add ORDER BY clause."""
53
        
54
    def limit(self, limit) -> QueryBuilder:
55
        """Add LIMIT clause."""
56
        
57
    def offset(self, offset) -> QueryBuilder:
58
        """Add OFFSET clause."""
59
        
60
    def distinct(self) -> QueryBuilder:
61
        """Add DISTINCT clause."""
62
        
63
    def get_sql(self, **kwargs) -> str:
64
        """Generate SQL string."""
65

66
class JoiningQueryBuilder(QueryBuilder):
67
    def on(self, criterion) -> QueryBuilder:
68
        """Specify JOIN condition."""
69
        
70
    def using(self, *terms) -> QueryBuilder:
71
        """Specify JOIN USING clause."""
72
```
73

74
**Usage Examples:**
75

76
```python
77
from pypika import Query, Table, Field
78
from pypika import JoinType, Order
79

80
users = Table('users')
81
orders = Table('orders')
82

83
# Basic SELECT
84
query = Query.from_(users).select('*')
85

86
# SELECT with specific columns
87
query = Query.from_(users).select(users.name, users.email, users.age)
88

89
# WHERE conditions
90
query = Query.from_(users).select('*').where(users.age >= 18)
91

92
# Multiple WHERE conditions
93
query = (Query.from_(users)
94
         .select('*')
95
         .where(users.age >= 18)
96
         .where(users.status == 'active'))
97

98
# JOIN operations
99
query = (Query.from_(users)
100
         .join(orders).on(users.id == orders.user_id)
101
         .select(users.name, orders.total))
102

103
# LEFT JOIN with complex conditions
104
query = (Query.from_(users)
105
         .left_join(orders).on((users.id == orders.user_id) & (orders.status == 'completed'))
106
         .select(users.name, orders.total))
107

108
# GROUP BY and HAVING
109
from pypika.functions import Count, Sum
110
query = (Query.from_(orders)
111
         .select(orders.user_id, Count('*').as_('order_count'), Sum(orders.total).as_('total_spent'))
112
         .groupby(orders.user_id)
113
         .having(Count('*') > 5))
114

115
# ORDER BY with multiple columns
116
query = (Query.from_(users)
117
         .select('*')
118
         .orderby(users.created_at, order=Order.desc)
119
         .orderby(users.name))
120

121
# LIMIT and OFFSET for pagination
122
query = (Query.from_(users)
123
         .select('*')
124
         .orderby(users.id)
125
         .limit(20)
126
         .offset(40))
127

128
# Subqueries
129
subquery = Query.from_(orders).select(orders.user_id).where(orders.total > 100)
130
query = Query.from_(users).select('*').where(users.id.isin(subquery))
131
```
132

133
### INSERT Queries
134

135
Build INSERT statements supporting single and multiple value insertion, column specification, and subquery insertion.
136

137
```python { .api }
138
class Query:
139
    @staticmethod
140
    def into(table) -> QueryBuilder:
141
        """Initialize INSERT query into table."""
142

143
class QueryBuilder:
144
    def insert(self, *terms) -> QueryBuilder:
145
        """Insert values into table."""
146
        
147
    def columns(self, *terms) -> QueryBuilder:
148
        """Specify target columns for INSERT."""
149
        
150
    def select(self, *terms) -> QueryBuilder:
151
        """Insert from SELECT subquery."""
152
```
153

154
**Usage Examples:**
155

156
```python
157
from pypika import Query, Table
158

159
users = Table('users')
160

161
# Simple INSERT with values
162
query = Query.into(users).insert(1, 'John Doe', 'john@example.com')
163

164
# INSERT with column specification
165
query = (Query.into(users)
166
         .columns(users.name, users.email)
167
         .insert('John Doe', 'john@example.com'))
168

169
# Multiple value INSERT
170
query = (Query.into(users)
171
         .columns(users.name, users.email)
172
         .insert('John Doe', 'john@example.com')
173
         .insert('Jane Smith', 'jane@example.com'))
174

175
# INSERT from SELECT
176
active_users = Table('active_users')
177
query = (Query.into(users)
178
         .columns(users.name, users.email)
179
         .select(active_users.name, active_users.email)
180
         .from_(active_users)
181
         .where(active_users.last_login > '2023-01-01'))
182
```
183

184
### UPDATE Queries
185

186
Build UPDATE statements with SET clauses, WHERE conditions, and JOIN support for complex updates.
187

188
```python { .api }
189
class Query:
190
    @staticmethod
191
    def update(table) -> QueryBuilder:
192
        """Initialize UPDATE query on table."""
193

194
class QueryBuilder:
195
    def set(self, field, value) -> QueryBuilder:
196
        """Set field to value."""
197
        
198
    def where(self, criterion) -> QueryBuilder:
199
        """Add WHERE conditions."""
200
        
201
    def join(self, table, how=JoinType.inner) -> JoiningQueryBuilder:
202
        """Add JOIN for complex updates."""
203
```
204

205
**Usage Examples:**
206

207
```python
208
from pypika import Query, Table
209
from pypika.functions import Now
210

211
users = Table('users')
212
orders = Table('orders')
213

214
# Simple UPDATE
215
query = (Query.update(users)
216
         .set(users.email, 'newemail@example.com')
217
         .where(users.id == 1))
218

219
# Multiple field UPDATE
220
query = (Query.update(users)
221
         .set(users.email, 'newemail@example.com')
222
         .set(users.updated_at, Now())
223
         .where(users.id == 1))
224

225
# UPDATE with JOIN
226
query = (Query.update(users)
227
         .join(orders).on(users.id == orders.user_id)
228
         .set(users.total_orders, orders.count)
229
         .where(orders.status == 'completed'))
230

231
# Conditional UPDATE
232
from pypika import Case
233
query = (Query.update(users)
234
         .set(users.status, 
235
              Case()
236
              .when(users.last_login > '2023-01-01', 'active')
237
              .else_('inactive'))
238
         .where(users.status.isnull()))
239
```
240

241
### DELETE Queries
242

243
Build DELETE statements with WHERE conditions and JOIN support for complex deletion operations.
244

245
```python { .api }
246
class Query:
247
    @staticmethod
248
    def from_(table) -> QueryBuilder:
249
        """Initialize query from table (use .delete() for DELETE operations)."""
250

251
class QueryBuilder:
252
    def delete(self) -> QueryBuilder:
253
        """Convert to DELETE query."""
254
        
255
    def where(self, criterion) -> QueryBuilder:
256
        """Add WHERE conditions."""
257
        
258
    def join(self, table, how=JoinType.inner) -> JoiningQueryBuilder:
259
        """Add JOIN for complex deletes."""
260
```
261

262
**Usage Examples:**
263

264
```python
265
from pypika import Query, Table
266

267
users = Table('users')
268
orders = Table('orders')
269

270
# Simple DELETE
271
query = Query.from_(users).delete().where(users.id == 1)
272

273
# DELETE with complex conditions
274
query = (Query.from_(users)
275
         .delete()
276
         .where((users.status == 'inactive') & (users.last_login < '2022-01-01')))
277

278
# DELETE with JOIN
279
query = (Query.from_(users)
280
         .join(orders).on(users.id == orders.user_id)
281
         .delete()
282
         .where(orders.status == 'cancelled'))
283
```
284

285
### DDL Operations
286

287
Data Definition Language operations for creating and dropping database objects.
288

289
```python { .api }
290
class Query:
291
    @staticmethod
292
    def create_table(table) -> CreateQueryBuilder:
293
        """Initialize CREATE TABLE query."""
294
        
295
    @staticmethod
296
    def drop_table(table) -> DropQueryBuilder:
297
        """Initialize DROP TABLE query."""
298
        
299
    @staticmethod
300
    def drop_database(database) -> DropQueryBuilder:
301
        """Initialize DROP DATABASE query."""
302
        
303
    @staticmethod
304
    def drop_user(user) -> DropQueryBuilder:
305
        """Initialize DROP USER query."""
306
        
307
    @staticmethod
308
    def drop_view(view) -> DropQueryBuilder:
309
        """Initialize DROP VIEW query."""
310
        
311
    @staticmethod
312
    def Table(table_name, **kwargs) -> Table:
313
        """Factory method for creating Table instances."""
314
        
315
    @staticmethod
316
    def Tables(*names, **kwargs) -> List[Table]:
317
        """Factory method for creating multiple Table instances."""
318

319
class CreateQueryBuilder:
320
    def columns(self, *columns) -> CreateQueryBuilder:
321
        """Specify table columns."""
322
        
323
    def primary_key(self, *columns) -> CreateQueryBuilder:
324
        """Define primary key."""
325
        
326
    def foreign_key(self, column, references_table, references_column) -> CreateQueryBuilder:
327
        """Define foreign key constraint."""
328

329
class DropQueryBuilder:
330
    def if_exists(self) -> DropQueryBuilder:
331
        """Add IF EXISTS clause."""
332
        
333
    def cascade(self) -> DropQueryBuilder:
334
        """Add CASCADE clause."""
335
```
336

337
**Usage Examples:**
338

339
```python
340
from pypika import Query, Table, Column
341

342
users = Table('users')
343

344
# CREATE TABLE
345
query = (Query.create_table(users)
346
         .columns(
347
             Column('id', 'INTEGER', nullable=False),
348
             Column('name', 'VARCHAR(100)', nullable=False),
349
             Column('email', 'VARCHAR(255)', nullable=False),
350
             Column('created_at', 'TIMESTAMP', default='CURRENT_TIMESTAMP')
351
         )
352
         .primary_key('id'))
353

354
# DROP TABLE
355
query = Query.drop_table(users).if_exists()
356

357
# DROP DATABASE
358
database = Database('test_db')
359
query = Query.drop_database(database).if_exists()
360

361
# DROP USER
362
query = Query.drop_user('test_user').if_exists()
363

364
# DROP VIEW
365
query = Query.drop_view('user_summary').if_exists()
366

367
# Factory methods for Table creation
368
users = Query.Table('users')
369
orders, products = Query.Tables('orders', 'products')
370
```
371

372
### Common Table Expressions (CTEs)
373

374
Build queries with Common Table Expressions for complex hierarchical and recursive queries.
375

376
```python { .api }
377
class Query:
378
    @staticmethod
379
    def with_(table, name) -> QueryBuilder:
380
        """Add Common Table Expression."""
381

382
class QueryBuilder:
383
    def with_(self, table, name) -> QueryBuilder:
384
        """Add additional CTE."""
385
```
386

387
**Usage Examples:**
388

389
```python
390
from pypika import Query, Table, AliasedQuery
391

392
users = Table('users')
393
orders = Table('orders')
394

395
# Simple CTE
396
user_stats = AliasedQuery('user_stats')
397
user_stats_query = (Query.from_(orders)
398
                    .select(orders.user_id, Count('*').as_('order_count'))
399
                    .groupby(orders.user_id))
400

401
query = (Query.with_(user_stats_query, 'user_stats')
402
         .from_(users)
403
         .join(user_stats).on(users.id == user_stats.user_id)
404
         .select(users.name, user_stats.order_count))
405

406
# Multiple CTEs
407
recent_orders = AliasedQuery('recent_orders')
408
recent_orders_query = (Query.from_(orders)
409
                       .select('*')
410
                       .where(orders.created_at > '2023-01-01'))
411

412
query = (Query.with_(user_stats_query, 'user_stats')
413
         .with_(recent_orders_query, 'recent_orders')
414
         .from_(users)
415
         .join(user_stats).on(users.id == user_stats.user_id)
416
         .join(recent_orders).on(users.id == recent_orders.user_id)
417
         .select(users.name, user_stats.order_count, recent_orders.total))
418
```
419

420
### Set Operations
421

422
Combine multiple queries using UNION, INTERSECT, and EXCEPT operations.
423

424
```python { .api }
425
class QueryBuilder:
426
    def union(self, other) -> QueryBuilder:
427
        """UNION with another query."""
428
        
429
    def union_all(self, other) -> QueryBuilder:
430
        """UNION ALL with another query."""
431
        
432
    def intersect(self, other) -> QueryBuilder:
433
        """INTERSECT with another query."""
434
        
435
    def except_(self, other) -> QueryBuilder:
436
        """EXCEPT with another query."""
437
```
438

439
**Usage Examples:**
440

441
```python
442
from pypika import Query, Table
443

444
active_users = Table('active_users')
445
inactive_users = Table('inactive_users')
446

447
# UNION queries
448
query1 = Query.from_(active_users).select(active_users.email)
449
query2 = Query.from_(inactive_users).select(inactive_users.email)
450

451
combined_query = query1.union(query2)
452
```