> For the complete documentation index, see [llms.txt](https://qb.ortusbooks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://qb.ortusbooks.com/14.0.0/query-builder/building-queries/wheres.md).

# Wheres

| Table of Contents             |                                     |                             |
| ----------------------------- | ----------------------------------- | --------------------------- |
| [where](#where)               | [andWhere](#andwhere)               | [orWhere](#orwhere)         |
| [whereBetween](#wherebetween) | [whereNotBetween](#wherenotbetween) | [whereColumn](#wherecolumn) |
| [whereExists](#whereexists)   | [whereNotExists](#wherenotexists)   | [whereLike](#wherelike)     |
| [whereIn](#wherein)           | [whereNotIn](#wherenotin)           | [whereRaw](#whereraw)       |
| [whereNull](#wherenull)       | [whereNotNull](#wherenotnull)       |                             |

## Where Methods

### where

| Name       | Type                                                                                          | Required | Default | Description                                                                                                                                                                                                                           |
| ---------- | --------------------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) \| Function | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query. A function can be passed to begin a nested where statement.                                    |
| operator   | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md)             | `false`  |         | The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the `operator` and the `value` left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).             |
| value      | any                                                                                           | `false`  |         | The value with which to constrain the column. An [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
| combinator | string                                                                                        | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the [`andWhere`](#andwhere) and [`orWhere`](#orwhere) methods instead.                        |

Adds a where clause to a query.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( "active", "=", 1 );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `active` = ?
```

{% endcode %}

{% hint style="info" %}
Using the `where` method will parameterize the `value` passed. If you want to constrain a column to another column, use the [`whereColumn`](#wherecolumn) method.
{% endhint %}

You can also pass an [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) as the value.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( "last_logged_in", ">", query.raw( "NOW()" ) );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()
```

{% endcode %}

Any of the following operators can be used in a where clause.

| Valid Operators |                |             |
| --------------- | -------------- | ----------- |
| =               | <              | >           |
| <=              | >=             | <>          |
| !=              | like           | like binary |
| not like        | between        | ilike       |
| &               | \|             | ^           |
| <<              | >>             | rlike       |
| regexp          | not regexp     | \~          |
| \~\*            | !\~            | !\~\*       |
| similar to      | not similar to |             |

When using the `"="` constraint, you can use a shortcut and define the value as the second argument.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( "active", 1 );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `active` = ?
```

{% endcode %}

{% hint style="info" %}
You may also use [dynamic where{Column}](#dynamic-where-methods) statements to simplify this further.
{% endhint %}

To group where statements together, pass a function to the where clause as the only parameter.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( function( q ) {
        q.where( "active", 1 )
            .where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
    } );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE (
    `active` = ?
    AND
    `last_logged_in` > ?
)
```

{% endcode %}

{% hint style="info" %}
This grouping can be nested as many levels as you require.
{% endhint %}

A Function or QueryBuilder can be used as a subselect expression when passed to `value`.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( "email", "foo" )
    .orWhere( "id", "=", function( q ) {
        q.select( q.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" );
    } );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `email` = ?
  OR `id` = (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
  )
```

{% endcode %}

### andWhere

| Name     | Type                                                                                          | Required | Default | Description                                                                                                                                                                                                                           |
| -------- | --------------------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column   | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) \| Function | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query. A function can be passed to begin a nested where statement.                                    |
| operator | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md)             | `false`  |         | The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the `operator` and the `value` left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).             |
| value    | any                                                                                           | `false`  |         | The value with which to constrain the column. An [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |

This method is simply an alias for [`where`](#where) with the combinator set to `"and"`.

### orWhere

| Name     | Type                                                                                          | Required | Default | Description                                                                                                                                                                                                                           |
| -------- | --------------------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column   | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) \| Function | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query. A function can be passed to begin a nested where statement.                                    |
| operator | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md)             | `false`  |         | The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the `operator` and the `value` left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).             |
| value    | any                                                                                           | `false`  |         | The value with which to constrain the column. An [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |

This method is simply an alias for [`where`](#where) with the combinator set to `"or"`.

### whereBetween

| Name       | Type                            | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | ------------------------------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| Expression            | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                 |
| start      | any \| Function \| QueryBuilder | `true`   |         | The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.                                                                                            |
| end        | any \| Function \| QueryBuilder | `true`   |         | The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.                                                                                                  |
| combinator | string                          | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods](#andwhere-and-orwhere) instead. |
| negate     | boolean                         | `false`  | `false` | False for BETWEEN, True for NOT BETWEEN.                                                                                                                                                                               |

Adds a where between clause to the query.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereBetween( "id", 1, 2 );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?
```

{% endcode %}

If a function or QueryBuilder is passed it is used as a subselect expression.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereBetween(
        "id",
        function( q ) {
            q.select( q.raw( "MIN(id)" ) )
                .from( "users" )
                .where( "email", "bar" );
        },
        builder.newQuery()
            .select( builder.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" )
    );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `id` BETWEEN (
    SELECT MIN(id)
    FROM `users`
    WHERE `email` = ?
)
AND (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
)
```

{% endcode %}

### whereNotBetween

| Name       | Type                            | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | ------------------------------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| Expression            | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                 |
| start      | any \| Function \| QueryBuilder | `true`   |         | The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.                                                                                            |
| end        | any \| Function \| QueryBuilder | `true`   |         | The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.                                                                                                  |
| combinator | string                          | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods](#andwhere-and-orwhere) instead. |

Adds a where not in clause to the query. This behaves identically to the [`whereBetween`](#wherebetween) method with the `negate`flag set to `true`. See the documentation for [`whereBetween`](#wherebetween) for usage and examples.

### whereColumn

| Name       | Type                                                                              | Required | Default | Description                                                                                                                                                                                                               |
| ---------- | --------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| first      | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) | `true`   |         | The name of the first column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                              |
| operator   | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) | `true`   |         | The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the `operator` and the `value` left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ). |
| second     | string \| Expression                                                              | `false`  |         | The name of the second column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                             |
| combinator | string                                                                            | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods](#andwhere-and-orwhere) instead.    |

Adds a where clause to a query that compares two columns.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereColumn( "first_name", "=", "last_name" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
```

{% endcode %}

Just as with `where`, when using `"="` as the operator you can use a shorthand passing the second column in as the operator and leaving the second column `null`.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereColumn( "first_name", "last_name" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
```

{% endcode %}

`Expressions` can be passed in place of either column.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)
```

{% endcode %}

### whereExists

| Name       | Type                     | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | ------------------------ | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| query      | Function \| QueryBuilder | `true`   |         | A function or QueryBuilder instance to be used as the exists subquery.                                                                                                                                                 |
| combinator | string                   | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead. |
| negate     | boolean                  | `false`  | `false` | False for EXISTS, True for NOT EXISTS.                                                                                                                                                                                 |

Adds a where exists clause to the query.

It can be configured with a function.

{% code title="QueryBuilder" %}

```javascript
query.from( "orders" )
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) )
            .from( "products" )
            .whereColumn( "products.id", "orders.id" );
    } );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)
```

{% endcode %}

It can also be configured with a QueryBuilder instance.

{% code title="QueryBuilder" %}

```javascript
var existsQuery = query.newQuery()
    .select( q.raw( 1 ) )
    .from( "products" )
    .whereColumn( "products.id", "orders.id" );

query.from( "orders" )
    .whereExists( existsQuery );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)
```

{% endcode %}

### whereNotExists

| Name       | Type                     | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | ------------------------ | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| query      | Function \| QueryBuilder | `true`   |         | A function or QueryBuilder instance to be used as the not exists subquery.                                                                                                                                             |
| combinator | string                   | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead. |

Adds a where not in clause to the query. This behaves identically to the [`whereExists`](#whereexists) method with the `negate`flag set to `true`. See the documentation for [`whereExists`](#whereexists) for usage and examples.

### whereLike

| Name       | Type                                                                              | Required | Default | Description                                                                                                                                                                                                                           |
| ---------- | --------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                                |
| value      | any                                                                               | `false`  |         | The value with which to constrain the column. An [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
| combinator | string                                                                            | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead.                |

A shortcut for calling [`where`](#where) with `"like"` set as the operator.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereLike( "username", "J%" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `username` LIKE ?
```

{% endcode %}

### whereNotLike

| Name       | Type                                                                              | Required | Default | Description                                                                                                                                                                                                                           |
| ---------- | --------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                                |
| value      | any                                                                               | `false`  |         | The value with which to constrain the column. An [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
| combinator | string                                                                            | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead.                |

A shortcut for calling [`where`](#where) with `"not like"` set as the operator.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereNotLike( "username", "J%" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `username` NOT LIKE ?
```

{% endcode %}

### whereIn

| Name       | Type                                                                                                                   | Required | Default | Description                                                                                                                                                                                                                                                                                                  |
| ---------- | ---------------------------------------------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| column     | string \| Expression                                                                                                   | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                                                                                                       |
| values     | string \| array \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) \| Function \| QueryBuilder | `true`   |         | A single value, list of values, or array of values to constrain a column with. [`Expressions`](/14.0.0/query-builder/building-queries/raw-expressions.md) may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression. |
| combinator | string                                                                                                                 | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead.                                                                                       |
| negate     | boolean                                                                                                                | `false`  | `false` | False for IN, True for NOT IN.                                                                                                                                                                                                                                                                               |

Adds a where in clause to the query.

The values passed to `whereIn` can be a single value, a list of values, or an array of values.

{% code title="QueryBuilder" %}

```javascript
query.from( "orders" )
    .whereIn( "id", [ 1, 4, 66 ] );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
```

{% endcode %}

{% hint style="warning" %}
Some database grammars have a hard limit on the number of parameters passed to a SQL statement. Keep this in mind while writing your queries.
{% endhint %}

If a list of values is passed in, it is converted to an array of values using a single comma (`","`) delimiter.

{% code title="QueryBuilder" %}

```javascript
query.from( "orders" )
    .whereIn( "id", "1,4,66" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
```

{% endcode %}

Any value in the list or array can also be passed using a [custom parameter type](/14.0.0/query-builder/building-queries/parameters-and-bindings.md#custom-parameter-types) to have more control over the parameter settings.

{% code title="QueryBuilder" %}

```javascript
query.from( "orders" )
    .whereIn( "id", [ 1, 4, { value = "66", cfsqltype = "CF_SQL_VARCHAR" } ] );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
```

{% endcode %}

`Expressions` can be freely mixed in with other values.

{% code title="QueryBuilder" %}

```javascript
query.from( "orders" )
    .whereIn( "id", [ query.raw( "MAX(id)" ), 4, 66 ] );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `orders`
WHERE `id` IN (MAX(id), ?, ?)
```

{% endcode %}

A function or QueryBuilder instance can be passed to be used as a subquery expression instead of a list of values.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereIn( "id", function( q ) {
        q.select( "id" )
            .from( "users" )
            .where( "age", ">", 25 );
    } );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE IN (
    SELECT `id`
    FROM `users`
    WHERE `age` > ?
)
```

{% endcode %}

{% hint style="warning" %}
You may find a `whereExists` method performs better for you than a `whereIn` with a subquery.
{% endhint %}

### whereNotIn

| Name       | Type                                                                                                                   | Required | Default | Description                                                                                                                                                                                                                                                                                                  |
| ---------- | ---------------------------------------------------------------------------------------------------------------------- | -------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| column     | string \| Expression                                                                                                   | `true`   |         | The name of the column or [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md) with which to constrain the query.                                                                                                                                                                       |
| values     | string \| array \| [Expression](/14.0.0/query-builder/building-queries/raw-expressions.md) \| Function \| QueryBuilder | `true`   |         | A single value, list of values, or array of values to constrain a column with. [`Expressions`](/14.0.0/query-builder/building-queries/raw-expressions.md) may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression. |
| combinator | string                                                                                                                 | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead.                                                                                       |

Adds a where not in clause to the query. This behaves identically to the `whereIn` method with the `negate`flag set to `true`. See the documentation for [`whereIn`](#wherein) for usage and examples.

### whereInBulk

| Name       | Type         | Required | Default  | Description                                                                                                  |
| ---------- | ------------ | -------- | -------- | ------------------------------------------------------------------------------------------------------------ |
| column     | string       | `true`   |          | The column to constrain.                                                                                     |
| values     | any \| array | `true`   |          | A value, list, or array of values to serialize into one bound parameter.                                     |
| sqlType    | string       | `false`  | inferred | The database SQL type used when expanding each value.                                                        |
| combinator | string       | `false`  | `"and"`  | The boolean combinator for the clause.                                                                       |
| negate     | boolean      | `false`  | `false`  | False for `IN`, true for `NOT IN`. Prefer the convenience methods instead of passing this argument directly. |

`whereInBulk` is an opt-in alternative to [`whereIn`](#wherein) for large collections. It serializes the values as JSON in one bound parameter, and the active grammar expands that parameter into rows. Regular `whereIn` behavior and performance are unchanged.

```javascript
query.from( "users" )
    .whereInBulk( "id", userIds )
    .get();
```

qb infers a common SQL type from the values. Matching `cfsqltype` values in custom parameter structs are preserved, and mixed values fall back to the grammar's string type. Pass `sqlType` when the database column requires a more specific type:

```javascript
query.from( "users" )
    .whereInBulk( "id", userIds, "BIGINT" )
    .get();
```

{% hint style="warning" %}
The explicit SQL type should match the constrained column so the database can avoid implicit conversions. SQL expressions are not supported as bulk values.
{% endhint %}

The following methods are also available:

* `whereNotInBulk`
* `andWhereInBulk`
* `orWhereInBulk`
* `andWhereNotInBulk`
* `orWhereNotInBulk`

Empty collections have the same behavior as `whereIn` and `whereNotIn`: an empty bulk `IN` never matches, while an empty bulk `NOT IN` always matches.

Bulk expansion support depends on database JSON features:

* SQL Server 2016+ using `OPENJSON`; database compatibility level 130+ is required.
* PostgreSQL 9.4+ using `JSONB_ARRAY_ELEMENTS_TEXT`.
* MySQL 8.0.4+ and MariaDB 10.6+ using `JSON_TABLE`.
* Oracle Database 12c Release 1 (12.1.0.2)+ using `JSON_TABLE`.
* SQLite with JSON functions enabled. They are built in by default as of SQLite 3.38.0.

Derby throws an `UnsupportedOperation` exception for non-empty bulk collections.

### JSON Where Methods

qb provides grammar-aware predicates for JSON containment, path existence, and array length. Each method accepts separate `column` and `path` arguments as its explicit form and the [`->` path syntax](/14.0.0/query-builder/building-queries/selects.md#json-scalar-paths) as a shortcut.

#### whereJsonContains

Check whether a JSON value contains a scalar or compound value.

```javascript
// explicit
query.whereJsonContains(
    column = "profile",
    path = [ "languages" ],
    value = "en"
);

// shortcut
query.whereJsonContains( "profile->languages", "en" );
```

Available variants are `orWhereJsonContains`, `whereJsonDoesntContain`, and `orWhereJsonDoesntContain`.

{% hint style="info" %}
Scalar containment is supported by MySQL, Postgres, SQL Server, Oracle, and SQLite. Compound array or struct containment values are supported by MySQL and Postgres; other grammars throw an `UnsupportedOperation` exception for compound values.
{% endhint %}

#### whereJsonExists

Check whether a JSON path exists.

```javascript
// explicit
query.whereJsonExists( column = "profile", path = [ "name" ] );

// shortcut
query.whereJsonExists( "profile->name" );
```

Available variants are `orWhereJsonExists`, `whereJsonDoesntExist`, and `orWhereJsonDoesntExist`.

#### whereJsonLength

Compare the length of a JSON array.

```javascript
// explicit
query.whereJsonLength(
    column = "profile",
    path = [ "languages" ],
    operator = ">",
    value = 1
);

// shortcut
query.whereJsonLength( "profile->languages", ">", 1 );
```

When the operator is omitted, qb uses equality:

```javascript
query.whereJsonLength( column = "profile", path = [ "languages" ], value = 2 );
query.whereJsonLength( "profile->languages", 2 );
```

Use `orWhereJsonLength` for an `OR` combinator.

### whereRaw

| Name          | Type   | Required | Default | Description                                                                                                                                                                                                            |
| ------------- | ------ | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| sql           | string | `true`   |         | The raw SQL to add to the query.                                                                                                                                                                                       |
| whereBindings | array  | `false`  | `[]`    | Any bindings needed for the raw SQL. Bindings can be simple values or [custom parameters](/14.0.0/query-builder/building-queries/parameters-and-bindings.md#custom-parameter-types).                                   |
| combinator    | string | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead. |

Shorthand to add a raw SQL statement to the where clauses.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereRaw(
        "id = ? OR email = ? OR is_admin = 1",
        [ 1, "foo" ]
    );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE id = ? OR email = ? OR is_admin = 1
```

{% endcode %}

### whereNull

| Name       | Type                 | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | -------------------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| Expression | `true`   |         | The name of the column to check if it is NULL. Can also pass an [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md).                                                                             |
| combinator | string               | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead. |
| negate     | boolean              | `false`  | `false` | False for NULL, True for NOT NULL.                                                                                                                                                                                     |

Adds a where null clause to the query.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereNull( "id" );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `id` IS NULL
```

{% endcode %}

### whereNotNull

| Name       | Type                 | Required | Default | Description                                                                                                                                                                                                            |
| ---------- | -------------------- | -------- | ------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| column     | string \| Expression | `true`   |         | The name of the column to check if it is NULL. Can also pass an [`Expression`](/14.0.0/query-builder/building-queries/raw-expressions.md).                                                                             |
| combinator | string               | `false`  | `"and"` | The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` [dynamic methods ](#andwhere-and-orwhere)instead. |
| negate     | boolean              | `false`  | `false` | False for NULL, True for NOT NULL.                                                                                                                                                                                     |

Adds a where not in clause to the query. This behaves identically to the [`whereNull`](#wherenull) method with the `negate`flag set to `true`. See the documentation for [`whereNull`](#wherenull) for usage and examples.

## Dynamic Where Methods

qb uses `onMissingMethod` to provide a few different helpers when working with `where...` methods.

### andWhere... and orWhere...

Every `where...` method in qb can be called prefixed with either `and` or `or`. Doing so will call the original method using the corresponding combinator.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .where( "username", "like", "j%" )
    .andWhere( function( q ) {
        q.where( "isSubscribed", 1 )
            .orWhere( "isOnFreeTrial", 1 );
     } );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND (
    `isSubscribed` = ?
    OR
    `isOnFreeTrial` = ?
  )
```

{% endcode %}

### where{Column}

If you call a method starting with `where` that does not match an existing qb method, qb will instead call the `where` method using the rest of the method name as the first column name. (The rest of the arguments will be shifted to account for this.) This also applies to `andWhere{Column}` and `orWhere{Column}` method signatures.

{% code title="QueryBuilder" %}

```javascript
query.from( "users" )
    .whereUsername( "like", "j%" )
    .whereActive( 1 );
```

{% endcode %}

{% code title="MySQL" %}

```sql
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND `active` = ?
```

{% endcode %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://qb.ortusbooks.com/14.0.0/query-builder/building-queries/wheres.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.