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.

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.

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.

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.

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.

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 in Salesforce Help.