---
title: "Select Queries"
description: "Select queries in Drizzle ORM are responsible for transforming high-level, type-safe builder configurations into raw SQL statements appropriate for the target database dialect (PostgreSQL, MySQL, o..."
last_updated: "2026-08-13T14:49:35.575027+00:00"
canonical_url: "https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical"
---

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [drizzle-orm/src/pg-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts)
- [drizzle-orm/src/gel-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/gel-core/dialect.ts)
- [drizzle-orm/type-tests/pg/select.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts)
- [drizzle-orm/src/mysql-core/dialect.ts](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts)
</details>

Select queries in Drizzle ORM are responsible for transforming high-level, type-safe builder configurations into raw SQL statements appropriate for the target database dialect (PostgreSQL, MySQL, or Gel). This subsystem provides the bridge between the developer's intuitive query builder API and the database-specific syntax requirements, ensuring that complex joins, conditional clauses, and set operations are rendered into valid, performant queries.

The architecture centers around dialect-specific classes—`PgDialect`, `GelDialect`, and `MySqlDialect`—which implement common query-building interfaces. By isolating dialect-specific concerns like quoting identifiers (`escapeName`), param handling (`escapeParam`), and specific SQL syntax patterns (like `limit` or `lockingClause`), the system maintains a unified developer experience regardless of the underlying database engine. 

At a high level, the flow involves taking a `SelectConfig` object, validating the query's structural integrity, building component chunks (e.g., CTEs, selections, joins, where clauses), and joining them into a final `SQL` object. This modular design allows Drizzle to handle complex features like nested relational queries and set operations consistently across different SQL dialects.

## The `buildSelectQuery` Mechanism

The `buildSelectQuery` method is the heart of the selection subsystem. It accepts a configuration object and orchestrates the assembly of various SQL components. A critical validation step occurs before building: it iterates over selected fields to ensure that every referenced column exists within the query's tables or joins.

If a field is found that does not belong to any table currently in the query, `buildSelectQuery` throws an error to prevent invalid SQL execution. This check is performed by comparing the table of the field with the table(s) defined in the query configuration, including those added via joins.

```typescript
// Example of validation logic (from PgDialect)
for (const f of fieldsList) {
    if (is(f.field, Column) && getTableName(f.field.table) !== getTableName(table) && !joins?.some(...)) {
        throw new Error(`Your field references a column "${tableName}"."${f.field.name}", but the table is not part of the query!`);
    }
}
```
Sources: [drizzle-orm/src/pg-core/dialect.ts:360-382](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L360-L382)

## Selection Construction

The `buildSelection` method manages the complexity of selecting columns versus expressions. It performs a case-based analysis on each field in the ordered selection list:

- **Aliased fields:** If flagged as a selection field, it uses the alias as an identifier.
- **SQL expressions/Aliased SQL:** It extracts the underlying SQL chunks. If `isSingleTable` is enabled, it attempts to normalize column references by applying the dialect-specific column casing.
- **Columns:** If `isSingleTable` is true, it replaces the column object with its cased identifier.
- **Subqueries:** It performs special decoder mapping to ensure the results are mapped correctly from the driver values.

The `isSingleTable` flag is crucial: it determines whether columns need to be prefixed with their table names, which is often unnecessary in single-table queries but required in joins to resolve ambiguity.

Sources: [drizzle-orm/src/pg-core/dialect.ts:211-277](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L211-L277)

## Handling Joins

The `buildJoins` mechanism processes an array of `JoinConfig` objects. It iterates through the meta-data, determining the join type (e.g., `inner`, `left`) and handling specific database features like `lateral` joins. 

The logic builds a dynamic `SQL` join array:
1. Adds a separator if it is the first join.
2. Identifies the table or view, applying schema prefixes and aliases where necessary.
3. Appends the join condition (`on` clause).
4. For MySql, it additionally processes `USE`, `FORCE`, and `IGNORE` index hints.

Sources: [drizzle-orm/src/pg-core/dialect.ts:279-325](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L279-L325), [drizzle-orm/src/mysql-core/dialect.ts:383-439](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/dialect.ts#L383-L439)

> [!TIP]
> Use `.leftJoin()` or `.innerJoin()` in the query builder to trigger these specific `buildJoins` branches. Ensuring your aliases are unique is critical, as they serve as the keys for later selection resolution.

## Set Operations (UNION, INTERSECT, EXCEPT)

When `setOperators` are present in the `SelectConfig`, `buildSelectQuery` delegates to `buildSetOperations`. This function uses recursion to collapse multiple set operations into a single query structure.

The recursive mechanism (`buildSetOperations`) pulls the first operator, processes it against the base query (`leftSelect`), and if there are more operators, it feeds the resulting `SQL` back into the function recursively. This effectively nests set operations, preserving order and precedence according to SQL standards.

Sources: [drizzle-orm/src/pg-core/dialect.ts:450-466](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L450-L466)

## Query Execution Lifecycle

The query lifecycle traces from the user-facing builder methods to the final dialect-specific execution.

```mermaid
flowchart TD
    A["User Call: select()"] --> B["Query Builder: build()"]
    B --> C["Dialect: buildSelectQuery()"]
    C --> D["Validation (Table checks)"]
    D --> E["Component Builders (With, From, Joins, Selection)"]
    E --> F["SQL Joining"]
    F --> G["Final SQL Object"]
    G --> H["Driver: Session Execution"]
```
Sources: [drizzle-orm/src/pg-core/dialect.ts:341-448](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L341-L448)

## Design Trade-offs

| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Separated Dialect Logic** | Enables database-specific syntax (e.g., MySQL vs PG) | Increases code duplication between dialects |
| **Recursive Set Ops** | Standardized handling of complex chaining | Increased call stack depth for long chains |
| **Pre-Query Validation** | Prevents runtime DB errors for missing joins | Slight performance overhead for complex queries |
| **CasingCache** | Abstracted casing rules | Memory usage for cache instances |

Sources: [drizzle-orm/src/pg-core/dialect.ts:63-71](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L63-L71), [drizzle-orm/src/pg-core/dialect.ts:450-466](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L450-L466)

## Working Example

The following example demonstrates building a complex joined query with custom SQL selection:

```typescript
import { sql } from 'drizzle-orm';
import { db } from './db';
import { users, cities } from './tables';

// The query builder will internally call PgDialect.buildSelectQuery()
const result = await db
    .select({
        id: users.id,
        text: users.text,
        city: {
            id: cities.id,
            name: cities.name,
        },
        custom: sql<string>`upper(${users.text})`.as('custom_text')
    })
    .from(users)
    .leftJoin(cities, eq(users.id, cities.id))
    .where(eq(users.id, 1));
```
Sources: [drizzle-orm/type-tests/pg/select.ts:205-233](https://github.com/blade47/drizzle-orm/blob/main/drizzle-orm/type-tests/pg/select.ts#L205-L233)

> [!CAUTION]
> The validation logic in `buildSelectQuery` is strict. If you use a column in a selection that belongs to a table not present in the `.from()` or `.join()` clauses, the dialect will throw an error immediately, preventing the query from being sent to the database. Always verify your join path.

## Related

- [Query Builder Core](https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-engine/query-builder-core)
- [SQL Expressions](https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/technical/query-engine/sql-expressions)


## Sitemap

See the full [sitemap](https://www.doc0.app/docs/e1b68fed-3c4e-4c95-b2ba-ebf050f78025/llms.txt) for all pages in this wiki.