**Source URL:** https://general.veevavault.dev/vql/clauses/where.md

# WHERE



Use the `WHERE` clause in VQL as a search filter to retrieve results that meet a specified condition.

* The `WHERE` clause supports a variety of [operators](/vql/operators), allowing you to further refine query results.

* Fields vary depending on the document or object being queried.

* Unless otherwise noted, the `WHERE` clause supports the same fields as the `SELECT` clause.

<Aside type="note">When querying documents, it is best practice to use [TONAME()](/vql/functions-options/toname) in the `WHERE` clause to provide the field name as the filter value. This ensures that the filter works for all users, including those with localized labels in their Vault.</Aside>

## Syntax {#Syntax}

```
SELECT {fields}
FROM {query target}
WHERE {field} {operator} {value}

```

## Functions & Options {#Functions_Options}

You can use the following functions and query target options in the `WHERE` clause. For full technical details, see the [VQL Functions & Options](/vql/functions-options) reference.

### Filtering Documents {#Filtering_Documents}

These functions are used to filter results when querying the `documents` target.

| Name | Description | API Version |
| --- | --- | --- |
| [`TONAME()`](/vql/functions-options/toname) | Filter by the document field name instead of the label. | v20.3+ |
| [`DELETEDSTATE()`](/vql/functions-options/state-functions) | Filter for documents in a deleted state. | v19.2+ |
| [`OBSOLETESTATE()`](/vql/functions-options/state-functions) | Filter for documents in an obsolete state. | v8.0+ |
| [`STEADYSTATE()`](/vql/functions-options/state-functions) | Filter for documents in a steady state. | v8.0+ |
| [`SUPERSEDEDSTATE()`](/vql/functions-options/state-functions) | Filter for documents in a superseded state. | v8.0+ |

### Filtering Object Records {#Filtering_Object_Records}

These functions are used to refine filters when querying Vault objects.

| Name | Description | API Version |
| --- | --- | --- |
| [`CASEINSENSITIVE()`](/vql/functions-options/caseinsensitive) | Bypass case sensitivity of field values. | v14.0+ |
| [`STATETYPE()`](/vql/functions-options/statetype) | Filter for object records with a specific state type. | v19.3+ |
| [`TOLABEL()`](/vql/functions-options/tolabel) | Filter by the object field label instead of the name. | v24.1+ |

### Filtering Attachments {#Filtering_Attachments}

These functions are used to filter by file-specific metadata when querying Attachment fields.

| Name | Description | API Version |
| --- | --- | --- |
| [`FILENAME()`](/vql/functions-options/attachment-field-functions) | Filter by the file name instead of the file handle. | v24.3+ |

## Operators {#Operators}

You can use all [comparison operators](/vql/operators/comparison-operators) and the following [logical operators](/vql/operators/logical-operators) in the `WHERE` clause:

| Name | Syntax | Description |
| --- | --- | --- |
| [`AND`](/vql/operators/logical-operators#AND) | `WHERE {field_1} = {value_1} AND {field_2} = {value_2}` | Field values match both specified conditions. |
| [`BETWEEN`](/vql/operators/logical-operators#BETWEEN) | `WHERE {field} BETWEEN {value_1} AND {value_2}` | Compare data between two different values. |
| [`CONTAINS`](/vql/operators/logical-operators#CONTAINS) | `WHERE {field} CONTAINS ({value_1},{value_2},{value_3})` | Field values match any of the specified values. |
| [`IN`](/vql/operators/logical-operators#IN) | `WHERE {field} IN (SELECT {fields} FROM {query target})` | Used for inner join relationship queries. |
| [`LIKE`](/vql/operators/logical-operators#LIKE) | `WHERE {field} LIKE '{value%}'` | Use wildcards `%` to match partial values. |
| [`OR`](/vql/operators/logical-operators#OR) | `WHERE {field_1} = {value_1} OR {field_2} = {v2}` | Field values match either specified condition. |

## Query Examples {#Query_Examples}

The following are examples of queries using `WHERE`.

### Query: Filter by Document Type {#Query_Filter_by_Document_Type}

The following query returns a list of documents of the *Commercial Content* document type.

```
SELECT id, name__v, status__v
FROM documents
WHERE TONAME(type__v) = 'commercial_content__c'

```

### Response {#Response}

```
{
    "responseStatus": "SUCCESS",
    "responseDetails": {
        "pagesize": 1000,
        "pageoffset": 0,
        "size": 6,
        "total": 6
    },
    "data": [
        {
            "id": 68,
            "name__v": "Cholecap Akathisia Temporally associated with Adult Major Depressive Disorder",
            "status__v": "Draft"
        },
        {
            "id": 65,
            "name__v": "Gludacta Package Brochure",
            "status__v": "Approved"
        },
        {
            "id": 64,
            "name__v": "Gludacta Logo Light",
            "status__v": "Approved"
        },
        {
            "id": 63,
            "name__v": "Gludacta Logo Dark",
            "status__v": "Approved"
        }
    ]
}

```

### Query: Retrieve Documents by Date or DateTime Value {#Query_Retrieve_Documents_by_Date_or_DateTime_Value}

The following query returns the ID and name of all documents created after October 31, 2015. The value `'2015-11-01'` corresponds to November 1st, 2015 at midnight (`00:00:00`), so results will include documents created on November 1st at `00:00:01` or later. Learn more about [Date and DateTime field values](/vql/references/language-specifications/data-types-formats#Date_Time_Formats).

```
SELECT id, name__v
FROM documents
WHERE document_creation_date__v > '2015-11-01'

```

### Query: Retrieve Products by Case-Insensitive Value {#Query_Retrieve_Products_by_Case_Insensitive_Value}

The following query returns results even if the field value is “Cholecap”, “choleCap”, or another case variation. Learn more about [case sensitivity in VQL queries](/vql/references/language-specifications/syntax-basics#Case_Sensitivity) and the [`CASEINSENSITIVE()`](/vql/functions-options/caseinsensitive) function.

```
SELECT id
FROM product__v
WHERE CASEINSENSITIVE(name__v) = 'cholecap'

```

### Query: Retrieve Products by State Type {#Query_Retrieve_Products_by_State_Type}

The following query returns all products in the *Complete* state. Learn more about the [`STATETYPE()`](/vql/functions-options/statetype) function.

```
SELECT id
FROM product__v
WHERE state__v = STATETYPE('complete_state__sys')

```

### Query: Retrieve Documents by Boolean Field Value {#Query_Retrieve_Documents_by_Boolean_Field_Value}

The following query returns all documents containing a Crosslink field with the value `true`. Learn more about [using boolean values](/vql/references/system-limits-performance/queryable-field-types#YesNo_Fields).

```
SELECT id, name__v
FROM documents
WHERE crosslink__v = true

```

### Query: Retrieve Documents with Null Field Values {#Query_Retrieve_Documents_with_Null_Field_Values}

The following query returns all documents with no value in the *External ID* field. Learn more about [using null values](/vql/references/language-specifications/result-handling#Null_Field_Values).

```
SELECT id, name__v
FROM documents
WHERE external_id__v = null

```


---

**Previous:** [SHOW](/vql/clauses/show)  
**Next:** [FIND](/vql/clauses/find)