0
# Query Construction
1

2
QueryDSL SQL provides a fluent, type-safe API for building SELECT queries with support for complex joins, conditions, grouping, ordering, and advanced SQL features like subqueries, CTEs, and window functions.
3

4
## Capabilities
5

6
### Query Factory
7

8
Creates new query instances and provides the primary entry point for query construction.
9

10
```java { .api }
11
/**
12
 * Creates a new empty query
13
 * @return New SQLQuery instance
14
 */
15
public SQLQuery<?> query();
16

17
/**
18
 * Creates a query with a single projection
19
 * @param expr Expression to select
20
 * @return SQLQuery with specified projection type
21
 */
22
public <T> SQLQuery<T> select(Expression<T> expr);
23

24
/**
25
 * Creates a query with multiple projections
26
 * @param exprs Expressions to select
27
 * @return SQLQuery with Tuple projection
28
 */
29
public SQLQuery<Tuple> select(Expression<?>... exprs);
30

31
/**
32
 * Creates a query selecting all columns from a table
33
 * @param expr Table path to select from
34
 * @return SQLQuery selecting from specified table
35
 */
36
public <T> SQLQuery<T> selectFrom(RelationalPath<T> expr);
37
```
38

39
**Usage Examples:**
40

41
```java
42
// Basic query creation
43
SQLQuery<?> query = queryFactory.query();
44

45
// Query with projection
46
SQLQuery<String> nameQuery = queryFactory.select(qUser.name);
47

48
// Query with multiple projections
49
SQLQuery<Tuple> userQuery = queryFactory.select(qUser.name, qUser.email, qUser.age);
50

51
// Query with table selection
52
SQLQuery<User> allUsers = queryFactory.selectFrom(qUser);
53
```
54

55
### Query Building
56

57
Core query building methods for constructing SELECT statements with FROM, WHERE, and other clauses.
58

59
```java { .api }
60
/**
61
 * Adds FROM clause to specify source tables
62
 * @param sources One or more table sources
63
 * @return Query builder for method chaining
64
 */
65
public Q from(Expression<?>... sources);
66

67
/**
68
 * Adds WHERE conditions to filter results
69
 * @param conditions Boolean expressions for filtering
70
 * @return Query builder for method chaining
71
 */
72
public Q where(Predicate... conditions);
73

74
/**
75
 * Adds GROUP BY clause for aggregation
76
 * @param columns Columns to group by
77
 * @return Query builder for method chaining
78
 */
79
public Q groupBy(Expression<?>... columns);
80

81
/**
82
 * Adds HAVING clause for aggregate filtering
83
 * @param conditions Boolean expressions for aggregate filtering
84
 * @return Query builder for method chaining
85
 */
86
public Q having(Predicate... conditions);
87

88
/**
89
 * Adds ORDER BY clause for result ordering
90
 * @param specifiers Order specifications
91
 * @return Query builder for method chaining
92
 */
93
public Q orderBy(OrderSpecifier<?>... specifiers);
94
```
95

96
**Usage Examples:**
97

98
```java
99
// Basic query with WHERE
100
List<User> activeUsers = queryFactory
101
    .selectFrom(qUser)
102
    .where(qUser.active.isTrue())
103
    .fetch();
104

105
// Query with multiple conditions
106
List<User> results = queryFactory
107
    .selectFrom(qUser)
108
    .where(qUser.age.gt(18)
109
        .and(qUser.country.eq("US"))
110
        .and(qUser.active.isTrue()))
111
    .orderBy(qUser.name.asc())
112
    .fetch();
113

114
// Aggregation query
115
List<Tuple> countByCountry = queryFactory
116
    .select(qUser.country, qUser.count())
117
    .from(qUser)
118
    .where(qUser.active.isTrue())
119
    .groupBy(qUser.country)
120
    .having(qUser.count().gt(10))
121
    .fetch();
122
```
123

124
### Join Operations
125

126
Type-safe join operations supporting inner, left, right, and full outer joins with automatic foreign key relationship detection.
127

128
```java { .api }
129
/**
130
 * Performs an inner join
131
 * @param target Join target expression
132
 * @return Query builder for method chaining
133
 */
134
public Q join(EntityPath<?> target);
135

136
/**
137
 * Performs an inner join with explicit condition
138
 * @param target Join target
139
 * @param condition Join condition
140
 * @return Query builder for method chaining
141
 */
142
public Q join(EntityPath<?> target, Predicate condition);
143

144
/**
145
 * Performs a left outer join
146
 * @param target Join target expression
147
 * @return Query builder for method chaining
148
 */
149
public Q leftJoin(EntityPath<?> target);
150

151
/**
152
 * Performs a right outer join
153
 * @param target Join target expression
154
 * @return Query builder for method chaining
155
 */
156
public Q rightJoin(EntityPath<?> target);
157

158
/**
159
 * Performs a full outer join
160
 * @param target Join target expression
161
 * @return Query builder for method chaining
162
 */
163
public Q fullJoin(EntityPath<?> target);
164
```
165

166
**Usage Examples:**
167

168
```java
169
// Simple join using foreign key
170
List<Tuple> userOrders = queryFactory
171
    .select(qUser.name, qOrder.total)
172
    .from(qUser)
173
    .join(qOrder).on(qUser.id.eq(qOrder.userId))
174
    .fetch();
175

176
// Left join with null handling
177
List<Tuple> usersWithOptionalOrders = queryFactory
178
    .select(qUser.name, qOrder.total.coalesce(0.0))
179
    .from(qUser)
180
    .leftJoin(qOrder).on(qUser.id.eq(qOrder.userId))
181
    .fetch();
182

183
// Multiple joins
184
List<Tuple> userOrderDetails = queryFactory
185
    .select(qUser.name, qOrder.total, qOrderItem.quantity)
186
    .from(qUser)
187
    .join(qOrder).on(qUser.id.eq(qOrder.userId))
188
    .join(qOrderItem).on(qOrder.id.eq(qOrderItem.orderId))
189
    .fetch();
190
```
191

192
### Subqueries
193

194
Support for correlated and non-correlated subqueries in SELECT, WHERE, and FROM clauses.
195

196
```java { .api }
197
/**
198
 * Creates a subquery for use in expressions
199
 * @return New query builder for subquery construction
200
 */
201
public <T> SQLQuery<T> select(Expression<T> expr);
202

203
/**
204
 * Checks if value exists in subquery results
205
 * @param subquery Subquery to check
206
 * @return Boolean expression for existence check
207
 */
208
public BooleanExpression exists(SubQueryExpression<?> subquery);
209

210
/**
211
 * Checks if expression value is in subquery results
212
 * @param subquery Subquery returning values to check against
213
 * @return Boolean expression for membership check
214
 */
215
public BooleanExpression in(SubQueryExpression<T> subquery);
216
```
217

218
**Usage Examples:**
219

220
```java
221
// EXISTS subquery
222
List<User> usersWithOrders = queryFactory
223
    .selectFrom(qUser)
224
    .where(JPAExpressions.exists(
225
        queryFactory.selectOne()
226
            .from(qOrder)
227
            .where(qOrder.userId.eq(qUser.id))
228
    ))
229
    .fetch();
230

231
// IN subquery
232
List<User> activeOrderUsers = queryFactory
233
    .selectFrom(qUser)
234
    .where(qUser.id.in(
235
        queryFactory.select(qOrder.userId)
236
            .from(qOrder)
237
            .where(qOrder.status.eq("ACTIVE"))
238
    ))
239
    .fetch();
240

241
// Scalar subquery in SELECT
242
List<Tuple> usersWithOrderCount = queryFactory
243
    .select(qUser.name, 
244
        queryFactory.select(qOrder.count())
245
            .from(qOrder)
246
            .where(qOrder.userId.eq(qUser.id)))
247
    .from(qUser)
248
    .fetch();
249
```
250

251
### Query Execution
252

253
Methods for executing queries and retrieving results in various formats.
254

255
```java { .api }
256
/**
257
 * Executes query and returns all results as a list
258
 * @return List of query results
259
 */
260
public List<T> fetch();
261

262
/**
263
 * Executes query and returns a single result
264
 * @return Single result or null if no results
265
 * @throws NonUniqueResultException if multiple results found
266
 */
267
public T fetchOne();
268

269
/**
270
 * Executes query and returns first result
271
 * @return First result or null if no results
272
 */
273
public T fetchFirst();
274

275
/**
276
 * Returns count of matching rows without fetching data
277
 * @return Number of matching rows
278
 */
279
public long fetchCount();
280

281
/**
282
 * Executes query with pagination
283
 * @return Query results page
284
 */
285
public QueryResults<T> fetchResults();
286
```
287

288
**Usage Examples:**
289

290
```java
291
// Fetch all results
292
List<User> allUsers = queryFactory
293
    .selectFrom(qUser)
294
    .fetch();
295

296
// Fetch single result
297
User user = queryFactory
298
    .selectFrom(qUser)
299
    .where(qUser.id.eq(1L))
300
    .fetchOne();
301

302
// Count results
303
long userCount = queryFactory
304
    .selectFrom(qUser)
305
    .where(qUser.active.isTrue())
306
    .fetchCount();
307

308
// Paginated results
309
QueryResults<User> page = queryFactory
310
    .selectFrom(qUser)
311
    .offset(20)
312
    .limit(10)
313
    .fetchResults();
314
```
315

316
### Window Functions
317

318
Support for SQL window functions including ranking, aggregation, and analytic functions.
319

320
```java { .api }
321
/**
322
 * Creates a ROW_NUMBER() window function
323
 * @return Window function for row numbering
324
 */
325
public static WindowFunction<Long> rowNumber();
326

327
/**
328
 * Creates a RANK() window function
329
 * @return Window function for ranking
330
 */
331
public static WindowFunction<Long> rank();
332

333
/**
334
 * Creates a LAG() window function
335
 * @param expr Expression to get previous value of
336
 * @return Window function for accessing previous row value
337
 */
338
public static <T> WindowFunction<T> lag(Expression<T> expr);
339

340
/**
341
 * Specifies the window frame for the function
342
 * @param orderBy Ordering specification for the window
343
 * @return Window function with ordering
344
 */
345
public WindowOver<T> over(OrderSpecifier<?>... orderBy);
346
```
347

348
**Usage Examples:**
349

350
```java
351
// Row numbering with ordering
352
List<Tuple> rankedUsers = queryFactory
353
    .select(qUser.name, 
354
        SQLExpressions.rowNumber().over(qUser.score.desc()).as("rank"))
355
    .from(qUser)
356
    .fetch();
357

358
// Running total
359
List<Tuple> runningTotals = queryFactory
360
    .select(qOrder.date, qOrder.amount,
361
        qOrder.amount.sum().over(qOrder.date.asc()).as("running_total"))
362
    .from(qOrder)
363
    .orderBy(qOrder.date.asc())
364
    .fetch();
365

366
// Previous value comparison
367
List<Tuple> withPrevious = queryFactory
368
    .select(qStock.date, qStock.price,
369
        SQLExpressions.lag(qStock.price).over(qStock.date.asc()).as("prev_price"))
370
    .from(qStock)
371
    .orderBy(qStock.date.asc())
372
    .fetch();
373
```
374

375
### Common Table Expressions (CTEs)
376

377
Support for WITH clauses and recursive common table expressions.
378

379
```java { .api }
380
/**
381
 * Creates a WITH clause builder
382
 * @param alias Alias for the CTE
383
 * @param query Query defining the CTE
384
 * @return WithBuilder for constructing WITH clauses
385
 */
386
public WithBuilder<R> with(Path<?> alias, SubQueryExpression<?> query);
387

388
/**
389
 * Adds a recursive CTE
390
 * @param alias Alias for the recursive CTE
391
 * @param query Query defining the recursive CTE
392
 * @return WithBuilder for method chaining
393
 */
394
public WithBuilder<R> withRecursive(Path<?> alias, SubQueryExpression<?> query);
395
```
396

397
**Usage Examples:**
398

399
```java
400
// Simple CTE
401
QUser qActiveUser = new QUser("active_user");
402
List<String> names = queryFactory
403
    .with(qActiveUser, 
404
        queryFactory.selectFrom(qUser)
405
            .where(qUser.active.isTrue()))
406
    .select(qActiveUser.name)
407
    .from(qActiveUser)
408
    .where(qActiveUser.age.gt(25))
409
    .fetch();
410

411
// Recursive CTE for hierarchical data
412
QEmployee qSub = new QEmployee("sub");
413
List<Employee> hierarchy = queryFactory
414
    .withRecursive(qSub,
415
        queryFactory.selectFrom(qEmployee)
416
            .where(qEmployee.managerId.isNull())
417
            .unionAll(
418
                queryFactory.selectFrom(qEmployee)
419
                    .join(qSub).on(qEmployee.managerId.eq(qSub.id))
420
            ))
421
    .selectFrom(qSub)
422
    .fetch();
423
```