---
schema_version: "1"
id: eb7fa1f943793ec8241a6214f2fd3d8ca9dfc91c7935521715b0926daeaa8848
title: Using SOQL and SOSL
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/platform/salesforce-large-data-volumes-bp/guides/cce/ldv-deployments/ldv-deployments-techniques-using-soql-and-sosl.md
canonical_url: https://developer.salesforce.com/docs/platform/salesforce-large-data-volumes-bp/guide/ldv-deployments-techniques-using-soql-and-sosl.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: e549fb4ab7f39181ad487b32e64d838331d9010dadf246ccf2fd1493cacca954
products:
  - Best Practices for Deployments with Large Data Volumes
  - Platform Developer Content
---

# Using SOQL and SOSL

A SOQL query is the equivalent of a `SELECT` SQL statement, and a SOSL query is a programmatic way of performing a text-based search.

| |SOQL|SOSL|
|--|----|----|
|Executes with|Database|Search indexes|
|Uses the|`query()` call|`search()` call|

Use SOQL when:

-   You know in which objects or fields the data resides.
-   You want to:

    -   Retrieve data from a single object or from multiple objects that are related to one another
    -   Count the number of records that meet specified criteria
    -   Sort results as part of the query
    -   Retrieve data from number, date, or checkbox fields

Use SOSL when:

-   You don’t know in which object or field the data resides, and you want to find it in the most efficient way possible.
-   You want to:

    -   Retrieve multiple objects and fields efficiently, and the objects might or might not be related to one another
    -   Retrieve data for a particular division in an organization using the divisions feature, and you want to find it in the most efficient way possible

Consider the following when using SOQL or SOSL.

-   Both SOQL `WHERE` filters and SOSL search queries can specify text you should look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses a `CONTAINS` term.
-   SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.
-   In some cases, when multiple `WHERE` filters are being used in SOQL, indexes can’t be used even though the fields in the `WHERE` clause can be indexed. In this situation, decompose the single query into multiple queries, each of which should have one `WHERE` filter, and then combine the results.
-   Executing a query with a `WHERE` filter that has null values for picklists or foreign key fields doesn’t use the index, and should be avoided.

    For example, the following customer query performs poorly.

    ```soql
    SELECT Contact__c, Max_Score__c, CategoryName__c, Category__Team_Name__c
    FROM Interest__c
    WHERE Contact__c != null
        AND Contact__c IN :contacts
        AND override__c != 0
        AND (
                (override__c != null AND override__c > 0)
                OR
                (score__c != null AND score__c > 0)
            )
        AND Category__c != null
        AND (
                (Category_Team_IsActive__c = true OR CategoryName__c IN :selectvalues)
                AND
                (
                    Category_Team_Name__c != null
                    AND
                    Category_Team_Name__c IN :selectTeamValues
                )
            )
    
    ```

    (Items preceded by colons, such as `:contacts`, are Apex variables. See [Using Apex Variables in SOQL and SOSL Queries](https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_variables.htm) in the *Apex Developer Guide*.) `Nulls` in the criteria prevented the use of indexes, and some of the criteria were redundant causing the execution time to increase. Design the data model so that it doesn’t rely on `nulls` as valid field values.

    The query can be rewritten as:

    ```soql
    SELECT Contact__c, Max_Score__c, CategoryName__c, Category_Team_Name__c
    FROM Interest__c
    WHERE Contact__c IN :contacts
        AND (override__c > 0 OR score__c > 0)
        AND Category__c != 'Default'
        AND (
                (Category_Team_IsActive__c = true OR CategoryName__c IN :selectvalues)
                AND
                Category_Team_Name__c IN :selectTeamValues
            )
    
    ```

    For the field `Category__c`, the value `Default` is substituted for `NULL`, allowing an index to be used for that field.

    As another example, if dynamic values are being used for the `WHERE` field, and null values can be passed in, don’t let the query run to determine there are no records; instead, check for nulls and avoid the query, if possible.

    A query to retrieve an account by its foreign key account number can look like this (in pseudo code).

    ```soql
    SELECT Name
       FROM Account
       WHERE Account_ID___c = :acctid;
    
    if (rows found == 0) return "Not Found"
    ```

    If `acctid` is `null`, the entire Account table is scanned row by row until all data is examined.

    It’s better to rewrite the code as:

    ```soql
    if (acctid != null) {
       SELECT Name
          FROM Account
          WHERE Account_Id___c = :acctid
    }
    else {
        return "Not Found"
    }
    ```

-   When designing custom query-search user interfaces, it’s important to:

    -   Keep the number of fields to be searched or queried to a minimum. Using many fields leads to many permutations, which can be difficult to tune.
    -   Determine whether SOQL, SOSL, or a combination of the two is appropriate for the search.