# Where (/docs/query-building/where)
The `where()` method filters rows based on conditions. It's the most flexible clause in the query builder, supporting simple comparisons, complex predicates, and ClickHouse-specific operators.
## Overview [#overview]
Use `where()` to:
- Filter rows by column values
- Chain multiple conditions (AND logic)
- Build complex expressions with OR logic
These examples use a typed standalone `db` client so the query builder stays the focus.
- Use ClickHouse functions and operators
- Work with arrays, tuples, and subqueries
## Basic Where Clauses [#basic-where-clauses]
### Simple Conditions [#simple-conditions]
```typescript
const users = await db.table('users')
.where('status', 'eq', 'active')
.select(['id', 'name', 'email'])
.execute();
```
### Multiple Conditions (AND) [#multiple-conditions-and]
```typescript
const users = await db.table('users')
.where('status', 'eq', 'active')
.where('age', 'gte', 18)
.where('country', 'eq', 'US')
.select(['id', 'name'])
.execute();
```
## Comparison Operators [#comparison-operators]
| Operator | Description | Example |
| --- | --- | --- |
| `eq` | Equal | `where('status', 'eq', 'active')` |
| `neq` | Not equal | `where('status', 'neq', 'deleted')` |
| `gt` | Greater than | `where('age', 'gt', 18)` |
| `gte` | Greater than or equal | `where('age', 'gte', 18)` |
| `lt` | Less than | `where('price', 'lt', 100)` |
| `lte` | Less than or equal | `where('price', 'lte', 100)` |
| `like` | Pattern matching | `where('email', 'like', '%@company.com')` |
| `notLike` | Not like pattern | `where('email', 'notLike', '%@spam.com')` |
| `in` | In array | `where('status', 'in', ['active', 'pending'])` |
| `notIn` | Not in array | `where('status', 'notIn', ['deleted', 'banned'])` |
| `between` | Inclusive range | `where('age', 'between', [18, 65])` |
### Comparison Examples [#comparison-examples]
```typescript
// Equal
await db.table('users')
.where('status', 'eq', 'active')
.execute();
// Not equal
await db.table('users')
.where('status', 'neq', 'deleted')
.execute();
// Greater than
await db.table('products')
.where('price', 'gt', 100)
.execute();
// Range
await db.table('users')
.where('age', 'between', [18, 65])
.execute();
// Pattern matching
await db.table('users')
.where('email', 'like', '%@gmail.com')
.execute();
// Array membership
await db.table('orders')
.where('status', 'in', ['pending', 'processing', 'shipped'])
.execute();
```
`where('id', 'in', [])` matches nothing. `where('id', 'notIn', [])` excludes nothing, so it behaves like no filter for that condition.
## Null Checks [#null-checks]
```typescript
// Is null
await db.table('users')
.whereNull('deleted_at')
.execute();
// Is not null
await db.table('users')
.whereNotNull('email')
.execute();
```
You can also express these directly with filter operators:
```typescript
await db.table('users')
.where('deleted_at', 'isNull', null)
.execute();
await db.table('users')
.where('email', 'isNotNull', null)
.execute();
```
## Tuple `IN` filters [#tuple-in-filters]
Use tuple `IN` when you need membership checks across multiple columns.
```typescript
await db.table('orders')
.where(['id', 'created_by'], 'inTuple', [[1, 123], [2, 456]])
.execute();
```
Single-column tuple `IN` is supported too:
```typescript
await db.table('users')
.where('id', 'inTuple', [[1], [2], [3]])
.execute();
```
The tuple shape must match the column shape. For example, `where(['id', 'created_by'], 'inTuple', [[1, 2, 3]])` now fails early instead of compiling into a broken query.
## OR Conditions [#or-conditions]
### orWhere() [#orwhere]
```typescript
const users = await db.table('users')
.where('status', 'eq', 'active')
.orWhere('status', 'eq', 'pending')
.select(['id', 'name', 'status'])
.execute();
```
### orWhere with Callback [#orwhere-with-callback]
```typescript
const users = await db.table('users')
.where('country', 'eq', 'US')
.orWhere((expr) =>
expr.and([
expr.fn('hasAny', 'tags', ['vip', 'premium']),
expr.fn('endsWith', 'email', expr.literal('@company.com')),
])
)
.execute();
```
## Function Predicates [#function-predicates]
For complex conditions, use predicate builder callbacks:
```typescript
const events = await db.table('events')
.where((expr) =>
expr.and([
expr.fn('hasAny', 'tags', ['launch', 'beta']),
expr.fn('endsWith', 'status', expr.literal('active')),
])
)
.execute();
```
### Predicate Helpers [#predicate-helpers]
| Helper | Purpose | Example |
| --- | --- | --- |
| `expr.fn(name, ...args)` | Call ClickHouse function | `expr.fn('hasAny', 'tags', ['a', 'b'])` |
| `expr.col(column)` | Explicit column reference | `expr.col('created_at')` |
| `expr.array(values)` | ClickHouse array literal | `expr.array([1, 2, 3])` |
| `expr.literal(value)` / `expr.value(value)` | Force literal value | `expr.literal('active')` |
| `expr.raw(sql)` | Inline raw SQL fragment | `expr.raw('date > now() - INTERVAL 1 DAY')` |
| `expr.and([...])` | Combine with AND | `expr.and([cond1, cond2])` |
| `expr.or([...])` | Combine with OR | `expr.or([cond1, cond2])` |
### Predicate Examples [#predicate-examples]
```typescript
// Using ClickHouse functions
await db.table('products')
.where((expr) =>
expr.fn('hasAny', 'categories', ['electronics', 'gadgets'])
)
.execute();
// Complex AND/OR
await db.table('events')
.where((expr) =>
expr.or([
expr.fn('startsWith', 'event_type', expr.literal('user_')),
expr.fn('startsWith', 'event_type', expr.literal('admin_')),
])
)
.execute();
// Combining multiple conditions
await db.table('orders')
.where((expr) =>
expr.and([
expr.fn('greater', expr.col('total'), expr.literal(1000)),
expr.fn('notEquals', expr.col('status'), expr.literal('cancelled')),
])
)
.execute();
```
## Where Groups [#where-groups]
Group conditions with parentheses:
```typescript
const users = await db.table('users')
.where('status', 'eq', 'active')
.whereGroup((builder) => {
builder
.where('country', 'eq', 'US')
.orWhere('country', 'eq', 'CA');
})
.execute();
// Generates: WHERE status = 'active' AND (country = 'US' OR country = 'CA')
```
```typescript
const results = await db.table('orders')
.whereGroup((builder) => {
builder
.where('status', 'eq', 'pending')
.orWhere('status', 'eq', 'processing');
})
.whereGroup((builder) => {
builder
.where('total', 'gte', 100)
.orWhere('priority', 'eq', 'high');
})
.execute();
// Generates: WHERE (status = 'pending' OR status = 'processing')
// AND (total >= 100 OR priority = 'high')
```
## Advanced IN Operators [#advanced-in-operators]
ClickHouse supports advanced IN operators for distributed queries, tuples, and subqueries.
### IN Operators Reference [#in-operators-reference]
| Operator | Description | Example |
| --- | --- | --- |
| `in` / `notIn` | Standard array membership | `where('id', 'in', [1, 2, 3])` |
| `globalIn` / `globalNotIn` | GLOBAL IN for distributed tables | `where('user_id', 'globalIn', [1, 2, 3])` |
| `inSubquery` / `globalInSubquery` | Subquery string | `where('id', 'inSubquery', 'SELECT id FROM users')` |
| `inTable` / `globalInTable` | Table reference | `where('user_id', 'inTable', 'active_users')` |
| `inTuple` / `globalInTuple` | Multi-column tuple membership | `where(['c1', 'c2'], 'inTuple', [[1, 2], [3, 4]])` |
### Standard IN [#standard-in]
```typescript
await db.table('users')
.where('id', 'in', [1, 2, 3, 4, 5])
.execute();
await db.table('orders')
.where('status', 'in', ['pending', 'processing', 'shipped'])
.execute();
```
### Tuple IN (Multi-column) [#tuple-in-multi-column]
```typescript
await db.table('events')
.where(['counter_id', 'user_id'], 'inTuple', [
[34, 123],
[101500, 456],
])
.execute();
```
### Subquery IN [#subquery-in]
```typescript
await db.table('orders')
.where('user_id', 'inSubquery', 'SELECT id FROM users WHERE status = "active"')
.execute();
```
### Table Reference IN [#table-reference-in]
```typescript
await db.table('events')
.where('user_id', 'inTable', 'active_users')
.execute();
```
### Global IN (Distributed Tables) [#global-in-distributed-tables]
```typescript
await db.table('distributed_events')
.where('user_id', 'globalIn', [1, 2, 3])
.execute();
```
## Conditional Where [#conditional-where]
Skip where clauses when values are null or undefined:
```typescript
function findUsers(filters: { status?: string; minAge?: number }) {
return db.table('users')
.where(filters.status ? ['status', 'eq', filters.status] : null)
.where(filters.minAge ? ['age', 'gte', filters.minAge] : null)
.select(['id', 'name', 'email'])
.execute();
}
// Only applies status filter
findUsers({ status: 'active' });
// Only applies age filter
findUsers({ minAge: 18 });
// Applies both filters
findUsers({ status: 'active', minAge: 18 });
```
## Type Safety [#type-safety]
TypeScript ensures operators match column types:
```typescript
// ✅ Valid - number column with number comparison
await db.table('users')
.where('age', 'gte', 18)
.execute();
// ✅ Valid - string column with string comparison
await db.table('users')
.where('status', 'eq', 'active')
.execute();
// ✅ Valid - array membership
await db.table('users')
.where('status', 'in', ['active', 'pending'])
.execute();
// ❌ Error - type mismatch (TypeScript may catch this)
await db.table('users')
.where('age', 'eq', 'not_a_number')
.execute();
```
## Examples [#examples]
### Date Range Filter [#date-range-filter]
```typescript
const events = await db.table('events')
.where('created_at', 'gte', '2024-01-01')
.where('created_at', 'lt', '2024-02-01')
.select(['id', 'type', 'created_at'])
.execute();
```
### Complex Filter with OR [#complex-filter-with-or]
```typescript
const products = await db.table('products')
.where('category', 'eq', 'electronics')
.orWhere((expr) =>
expr.and([
expr.fn('hasAny', 'tags', ['featured', 'new']),
expr.fn('greater', expr.col('stock'), expr.literal(10)),
])
)
.execute();
```
### Conditional Filtering [#conditional-filtering]
```typescript
function searchUsers(query: string, filters: UserFilters) {
return db.table('users')
.where('name', 'like', \`%\${query}%\`)
.where(filters.status ? ['status', 'eq', filters.status] : null)
.where(filters.country ? ['country', 'eq', filters.country] : null)
.where(filters.minAge ? ['age', 'gte', filters.minAge] : null)
.execute();
}
```
### Advanced Tuple IN [#advanced-tuple-in]
```typescript
const results = await db.table('events')
.where(['event_type', 'user_id'], 'inTuple', [
['page_view', 123],
['page_view', 456],
['click', 123],
])
.execute();
```