---
schema_version: "1"
id: 5f5e4405e87d84eda9e8e31f2418bafb3a37b1c7902553bbe3727f6aa2d8b04f
title: Filter Your Query Results
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/guides/get-started-dc-sql/filter-query-results.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/guide/filter-query-results.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 11d907deb7cffcb004ef8367fe5a46b50826d91c37a15c2254c73fa8e8b9a49e
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# Filter Your Query Results

A query can return many rows, depending on the amount of data stored in Data 360. You can filter the retrieved rows by using a `WHERE` clause.

This example shows a field expression in the `WHERE` clause containing one field. It filters the result set to return only one Individual record with the specified ID value.

```
SELECT  "ssot__FirstName__c",
        "ssot__LastName__c",
        "ssot__Salutation__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__Id__c" = '003Hu00003SELnNIAX'

```

This query returns exactly one record that matches the specified ID.

| `ssot__FirstName__c` | `ssot__LastName__c` | `ssot__Salutation__c` |
| -------------------- | ------------------- | --------------------- |
| Edna                 | Frank               | Ms.                   |

The previous query filters on the ID field. You can add more than one filter condition in the `WHERE` clause by combining them with the `AND` and `OR` logical operators. For example, instead of retrieving only one record, you can retrieve all individuals whose last name is either `Frank` or `Green` and whose birth date field isn’t null.

```
SELECT  "ssot__Id__c",
        "ssot__FirstName__c",
        "ssot__LastName__c",
        "ssot__BirthDate__c"
FROM "ssot__Individual__dlm"
WHERE ("ssot__LastName__c" = 'Frank' OR "ssot__LastName__c" = 'Green')
      AND "ssot__BirthDate__c" IS NOT NULL

```

This query output shows sample results.

| `ssot__Id__c`        | `ssot__FirstName__c` | `ssot__LastName__c` | `ssot__BirthDate__c`       |
| -------------------- | -------------------- | ------------------- | -------------------------- |
| `003Hu00003SELnIIAX` | `Avi`                | `Green`             | `1937-03-12T00:00:00.000Z` |
| `003Hu00003SELnNIAX` | `Edna`               | `Frank`             | `1945-10-23T00:00:00.000Z` |

Another possible scenario is that you want to retrieve all the Generation X individuals with birth dates from 1965 through 1980. To specify a date range to filter on, the `WHERE` clause uses the `BETWEEN` operator. The `BETWEEN` operator compares the birth date against a range defined by two values and includes the beginning and ending values. In addition to the `BETWEEN` operator, this query uses the `TIMESTAMP` operator to convert the string values to timestamps so that they can be compared.

```
SELECT  "ssot__Id__c",
        "ssot__FirstName__c",
        "ssot__LastName__c",
        "ssot__BirthDate__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__BirthDate__c" BETWEEN TIMESTAMP '1965-01-01T00:00:00.000Z'
      AND TIMESTAMP '1980-12-31T00:00:00.000Z'

```

This query output shows sample results.

| `ssot__Id__c`        | `ssot__FirstName__c` | `ssot__LastName__c` | `ssot__BirthDate__c`       |
| -------------------- | -------------------- | ------------------- | -------------------------- |
| `003Hu00003SELn6IAH` | `Rose`               | `Gonzalez`          | `1971-08-13T00:00:00.000Z` |
| `003Hu00003SELnMIAX` | `Liz`                | `D'Cruz`            | `1965-05-18T00:00:00.000Z` |

## Pattern Matching in String Values

Sometimes, you want to search for records based on some character patterns. For example, you want to get all individuals whose first name starts with the letter 'E'. Use the `LIKE` operator to compare patterns in string values. Pattern matching is case-sensitive. You can use these symbols in the string pattern.

- An underscore (`_`) stands for any single character.
- A percent sign (`%`) matches any sequence of zero or more characters.

This query returns all individuals whose first name starts with 'E'. For example, a sample result can include individuals with the first names of Eugena and Edna.

```
SELECT  "ssot__Id__c",
        "ssot__FirstName__c",
        "ssot__LastName__c",
        "ssot__BirthDate__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__FirstName__c" LIKE 'E%'

```

This query output shows sample results. Eugena has no birth date value, so the `ssot__BirthDate__c` column for Eugena is empty.

| `ssot__Id__c`        | `ssot__FirstName__c` | `ssot__LastName__c` | `ssot__BirthDate__c`       |
| -------------------- | -------------------- | ------------------- | -------------------------- |
| `003Hu00003SELnNIAX` | `Edna`               | `Frank`             | `1945-10-23T00:00:00.000Z` |
| `00QHu00003W7JISMA3` | `Eugena`             | `Luce`              |                            |

This query uses two underscores in the `LIKE` pattern. It finds all individuals whose first name starts with 'J', ends with 'e', and is four characters long. A sample result can include individuals with the first names of Jake and Jane.

```
SELECT  "ssot__Id__c",
        "ssot__FirstName__c",
        "ssot__LastName__c",
        "ssot__BirthDate__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__FirstName__c" LIKE 'J__e'

```

This query output shows sample results. The two matching records are for Jake and Jane. Jake has no birthdate value, so the `ssot__BirthDate__c` column for Jake is empty.

| `ssot__Id__c`        | `ssot__FirstName__c` | `ssot__LastName__c` | `ssot__BirthDate__c`       |
| -------------------- | -------------------- | ------------------- | -------------------------- |
| `003Hu00003SELnPIAX` | `Jake`               | `Llorrac`           |                            |
| `003Hu00003SELnGIAX` | `Jane`               | `Grey`              | `1947-10-01T00:00:00.000Z` |

## Comparison Operators

Besides `LIKE` and `BETWEEN` operators, SQL also supports the commonly known comparison operators:

- `>`
- `>=`
- `<`
- `<=`
- `=`
- `<>`, not equals
- `!=`, an alternative syntax for `<>`

## Filter on a Secondary Index

A secondary index speeds up filters and joins on a non-primary-key field and can reduce credit usage.

To filter with a secondary index, refer to the indexed field directly. Equality (`=`) and range comparisons (`>`, `>=`, `<`, `<=`, and `BETWEEN`) on the indexed field use the index. The engine can't use the index when the indexed field is wrapped in a function or calculation, or matched with a `LIKE` pattern.

This filter uses the index because it's a direct match on the indexed field.

```
WHERE "ssot__LastName__c" = 'Frank'
```

When a function hides the indexed field, rewrite the condition to compare the field directly. For example, instead of filtering on a birth year with `YEAR()`, use a range on the field itself.

```
-- Not indexable: the function hides the indexed field
WHERE YEAR("ssot__BirthDate__c") = 1965

-- Indexable: a direct range on the indexed field
WHERE "ssot__BirthDate__c" >= TIMESTAMP '1965-01-01T00:00:00.000Z'
      AND "ssot__BirthDate__c" < TIMESTAMP '1966-01-01T00:00:00.000Z'
```

When you filter on an indexed field along with other fields, combine the conditions with `AND`. The secondary index narrows the result set first, and then the remaining conditions are evaluated against the smaller set.

```
WHERE "ssot__LastName__c" = 'Frank'
      AND "ssot__BirthDate__c" IS NOT NULL
```

When you join on a non-primary field, filtering the indexed side to significantly fewer rows than the other side gives the best performance.

For secondary index creation, supported object types, and limitations, see [Optimize Queries with Secondary Indexes](https://help.salesforce.com/s/articleView?language=en_US&id=data.c360_a_query_secondary_indexes.htm&type=5) in Salesforce Help.