Mastering SOQL and SOSL: Advanced Query Techniques for Salesforce

For Salesforce Admins and Developers, SOQL (Salesforce Object Query Language) and SOSL (Salesforce Object Search Language) are essential tools. They help you search, filter, and manage data across the platform, powering automation, reporting, and custom development.

The short answer to SOQL vs SOSL: use SOQL when you know which object and field hold the data, and SOSL when you know the word but not where it lives. This guide covers both, with the syntax, the Apex code, the limits and the mistakes that catch people out.

SOQL vs SOSL at a Glance

Why SOQL and SOSL Matter

Both SOQL and SOSL enable powerful interactions with your Salesforce data:

• Retrieve specific records based on flexible criteria
• Power dynamic Apex logic, batch classes, and triggers
• Search across multiple objects in a single query
• Build efficient and scalable reports
• Optimize performance by narrowing query scope

SOQL Basics: What You Need to Know

SOQL is similar to SQL but built around Salesforce objects. You use it to fetch specific fields from standard or custom objects. It’s perfect when you know where to look and what to retrieve.

SELECT FirstName, LastName, Email FROM Contact WHERE LastName = 'Smith'

This query returns the contacts whose last name is Smith.

Key Components of SOQL

• SELECT: fields to retrieve
• FROM: object to query
• WHERE: conditions for filtering
• ORDER BY: sort results
• LIMIT: cap the number of returned records

SELECT Name, Industry FROM Account WHERE Industry = 'Technology' ORDER BY Name LIMIT 10

Filters You Will Use Every Day

Pattern match with LIKE. % stands for any number of characters and _ for exactly one.

SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'

Match one of several values with IN.

SELECT Id, Name FROM Contact WHERE MailingCountry IN ('India', 'United Arab Emirates')

Use date literals so you never have to calculate dates yourself.

SELECT Id, Subject FROM Case WHERE CreatedDate = LAST_N_DAYS:30
SELECT Id, Name FROM Opportunity WHERE CloseDate = THIS_MONTH AND Amount > 50000

Check that a field has a value.

SELECT Id, Name FROM Contact WHERE Email != null

SOSL Overview: Search Across Objects

SOSL searches text, email, phone, and name fields across multiple objects in one go. Unlike SOQL, it doesn’t need the objects to be related.

FIND 'Acme' IN ALL FIELDS RETURNING Account(Name), Contact(FirstName, LastName)

This searches both Account and Contact for records that contain the word Acme.

Benefits of SOSL

• Keyword search, like the global search box
• Cross-object search in one call
• Useful for search screens and lookups where the user can type anything

Search Groups: Where SOSL Looks

Narrowing the search group is the easiest way to make a search faster and its results cleaner.

Filter, Sort and Limit Inside RETURNING

Each object in RETURNING can carry its own WHERE, ORDER BY and LIMIT.

FIND 'Acme*' IN NAME FIELDS
RETURNING Account(Id, Name WHERE Industry = 'Technology' ORDER BY Name LIMIT 10),
          Contact(Id, FirstName, LastName)

Advanced SOQL Techniques

Aggregation

Use aggregate functions like COUNT(), SUM(), or AVG() for grouped summaries.

SELECT Account.Name, COUNT(Id) FROM Contact GROUP BY Account.Name

This counts the number of Contacts per Account. Add HAVING to filter on the result of the aggregate:

SELECT AccountId, COUNT(Id) total FROM Contact GROUP BY AccountId HAVING COUNT(Id) > 5

Relationship Queries

SOQL supports two directions.

Child to parent (upward). Use dot notation. You can go up to five levels.

SELECT Name, Account.Name FROM Contact WHERE Account.Industry = 'Finance'

Parent to child (downward). Use a nested query with the child relationship name.

SELECT Name, (SELECT LastName FROM Contacts) FROM Account WHERE Industry = 'Finance'

For custom relationships, the name ends in __r, for example Account.Region__r.Name going up or (SELECT Id FROM Invoices__r) going down.

Subqueries

Use a subquery in the WHERE clause to filter one object based on another.

SELECT Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Doe')

This fetches all Accounts that have a Contact with the last name Doe. Swap IN for NOT IN to find what is missing, such as accounts with no open opportunities:

SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)

Advanced SOSL Techniques

Wildcards and Logic

SOSL supports the wildcards * (zero or more characters) and ? (exactly one character), in the middle or at the end of a term, plus the operators AND, OR and AND NOT.

FIND 'Acme*' IN NAME FIELDS RETURNING Account(Name)
FIND 'Acme AND NOT Ltd' IN NAME FIELDS RETURNING Account(Name)
FIND '"Acme Corporation"' IN NAME FIELDS RETURNING Account(Name)

The first returns accounts with a word starting with Acme. The second excludes names containing Ltd. The third, with double quotes inside, matches the exact phrase. A search term must be at least two characters long.

Multi-Object RETURNING

SOSL can return fields from several objects at once.

FIND 'Acme' IN ALL FIELDS RETURNING Account(Name), Contact(FirstName, LastName)

Using SOQL and SOSL in Apex

In Apex, both go inside square brackets. The difference is what comes back.

// SOQL: a list of one object type. Use a bind variable (:lastName) for values.
String lastName = 'Smith';
List<Contact> smiths = [
    SELECT Id, FirstName, Email FROM Contact WHERE LastName = :lastName
];

// SOSL: a list of lists, one inner list per object, in the order of RETURNING
List<List<SObject>> found = [
    FIND 'Acme*' IN NAME FIELDS
    RETURNING Account(Id, Name), Contact(Id, FirstName, LastName)
];
List<Account> accounts = (List<Account>) found[0];
List<Contact> contacts = (List<Contact>) found[1];

One syntax detail trips people up. In Apex the search term goes in single quotes: FIND 'Acme*'. In the Developer Console Query Editor and the API it goes in braces: FIND {Acme*}.

Dynamic Queries

When the query text is built at run time, use Database.query for SOQL and Search.query for SOSL. Never paste user input straight into the string.

// SOQL: bind variables work inside the string too
String industry = selectedIndustry;
List<Account> accounts = Database.query(
    'SELECT Id, Name FROM Account WHERE Industry = :industry'
);

// SOSL: escape what the user typed
String term = String.escapeSingleQuotes(userInput);
List<List<SObject>> found = Search.query(
    'FIND \'' + term + '*\' IN NAME FIELDS RETURNING Account(Id, Name)'
);

Large Results and Security

If a query can return thousands of rows, loop over it directly. Salesforce hands you the records 200 at a time instead of loading them all into memory.

for (List<Account> batch : [SELECT Id, Name FROM Account WHERE Industry = 'Technology']) {
    // work on 200 records at a time
}

Add WITH USER_MODE to make a SOQL query respect the running user’s object permissions, field-level security and sharing.

List<Account> visible = [SELECT Id, Name, AnnualRevenue FROM Account WITH USER_MODE];

The Same Job, Both Ways

A support agent types an email address and wants to know who it belongs to. The person could be a Contact or a Lead.

With SOQL you need two queries, one per object:

SELECT Id, Name FROM Contact WHERE Email = 'jane@acme.com'
SELECT Id, Name FROM Lead WHERE Email = 'jane@acme.com'

With SOSL, one search covers both:

FIND 'jane@acme.com' IN EMAIL FIELDS RETURNING Contact(Id, Name), Lead(Id, Name)

Now flip it. You need every contact at accounts in the Finance industry, with the account name. That is a relationship plus an exact filter, so SOQL does it in one query and SOSL can’t do it at all.

SOQL vs. SOSL: When to Use What

Use SOQL when you know exactly what data you’re retrieving. Use SOSL when you’re not sure where that data lives. In practice:

Governor Limits for SOQL and SOSL

Common Use Cases

• SOQL: fetch opportunities, contacts, or accounts for record detail pages
• SOQL with aggregates: show average deal size or total revenue
• SOSL: global search bars or search screens in Lightning components

Best Practices for Queries

• Select only the fields you need
• Filter with WHERE so you don’t pull records you won’t use
• Filter on indexed fields (Id, Name, OwnerId, CreatedDate, lookups, External IDs) on large objects
• Use bind variables in Apex instead of building strings
• Prefer one relationship query over several separate queries
• Keep queries and searches out of loops
• In SOSL, name the search group and the objects you want instead of searching everything

For more on keeping queries fast as data grows, see managing large data volumes.

Testing Code That Uses SOSL

Records created in a test are not in the search index, so a SOSL search in a test returns nothing unless you tell it what to return. Test.setFixedSearchResults does that.

@isTest
static void findsAccountByName() {
    Account acme = new Account(Name = 'Acme Corporation');
    insert acme;

    Test.setFixedSearchResults(new List<Id>{ acme.Id });

    Test.startTest();
    List<List<SObject>> found = [FIND 'Acme*' IN NAME FIELDS RETURNING Account(Id, Name)];
    Test.stopTest();

    Assert.areEqual(1, found[0].size(), 'The account should be found');
}

SOQL needs no such step. It sees the records your test inserted.

Troubleshooting SOQL and SOSL Issues

FAQs

Can I mix SOQL and SOSL in the same query?

No, they run separately. You can run both in the same Apex method.

Can SOQL do fuzzy searches like SOSL?

SOQL has LIKE for patterns such as Name LIKE 'Acme%'. It matches characters in one field. SOSL looks for words anywhere in the searchable fields of several objects, which is what people usually mean by search.

Which is better for cross-object queries?

Use SOQL for related objects. Use SOSL for unrelated objects or text search.

Which is faster?

It depends on the question. SOQL with a filter on an indexed field is very fast. For finding text across large objects, SOSL usually wins because it reads the search index instead of scanning records.

Are there governor limits for queries?

Yes. A synchronous transaction allows 100 SOQL queries and 20 SOSL searches.

Can I run these queries outside of code?

Yes. Use the Query Editor in the Developer Console, the SOQL Builder in VS Code, or a tool like Workbench.

Where can I practise?

My SOQL in Salesforce crash course walks through these queries step by step in a real org.