Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 36 ยท SOQL and DML

Filtering and Sorting with SOQL

Filter SOQL results with WHERE, operators, LIKE, IN, date literals and ORDER BY, plus LIMIT and OFFSET paging, NULLS handling and dynamic filters, with stand-ins.

A query that returns every record is rarely what you want. The WHERE clause narrows the rows, ORDER BY sorts them, and LIMIT/OFFSET control how many you get. Filtering in the database, not in Apex loops, is the single best way to keep code fast and inside governor limits.

Real SOQL is read-only here. The runnable examples are Java stand-ins that mimic the filtering and sorting on in-memory records.

WHERE and comparison operators

SQL
SELECT Id, Name, AnnualRevenue
FROM Account
WHERE Industry = 'Energy' AND AnnualRevenue > 1000000
Operator Meaning
= != Equal, not equal (string comparison ignores case)
< <= > >= Comparisons
AND OR NOT Combine conditions (use parentheses to be clear)
IN NOT IN Match any value in a list
LIKE Pattern match with % (any text) and _ (one character)
INCLUDES EXCLUDES For multi-select picklists
SQL
WHERE Name LIKE 'Acme%'                     -- starts with Acme
WHERE Industry IN ('Energy', 'Technology')
WHERE Stage__c != null                      -- has a value
WHERE (Industry = 'Energy' OR Industry = 'Utilities') AND Rating = 'Hot'

Compare with null using = null or != null. Mixing AND and OR without parentheses is a classic mistake; AND binds tighter than OR.

Using Apex variables

Java
Decimal minRevenue = 1000000;
Set<String> industries = new Set<String>{ 'Energy', 'Technology' };

List<Account> result = [
    SELECT Id, Name
    FROM Account
    WHERE Industry IN :industries AND AnnualRevenue >= :minRevenue
];

Date literals

SOQL has built-in relative dates, so you do not compute them in Apex:

SQL
WHERE CreatedDate = TODAY
WHERE CloseDate >= THIS_MONTH
WHERE CreatedDate = LAST_N_DAYS:30
WHERE CloseDate = NEXT_QUARTER
WHERE LastModifiedDate < LAST_YEAR

Date fields take a YYYY-MM-DD literal without quotes (WHERE CloseDate > 2026-01-01); datetime fields use 2026-01-01T00:00:00Z. Remember that datetimes are stored in UTC.

ORDER BY, LIMIT and OFFSET

SQL
SELECT Name, AnnualRevenue
FROM Account
WHERE AnnualRevenue != null
ORDER BY AnnualRevenue DESC NULLS LAST, Name ASC
LIMIT 5
OFFSET 10
  • Sort ascending (ASC, the default) or descending (DESC); add several sort fields separated by commas.
  • NULLS FIRST / NULLS LAST controls where empty values land.
  • LIMIT n returns at most n rows. A "top 5" query is ORDER BY ... DESC LIMIT 5.
  • OFFSET n skips n rows, useful for paging, but it is capped at 2,000. For deep paging, filter by Id or a sort key instead.

A stand-in: filter, sort, page

The stream below does what WHERE, ORDER BY ... DESC and LIMIT do. Notice how the order of steps matches the order SOQL applies them: filter first, then sort, then limit.

Java Stand-in (Java): WHERE, ORDER BY, LIMIT

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

LIKE patterns in a stand-in

LIKE uses % and _. A regular expression can model it in Java:

Java Stand-in (Java): LIKE patterns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Performance: selective filters

Salesforce keeps indexes on Id, Name, lookup fields, CreatedDate, LastModifiedDate and fields marked External Id or Unique. A filter on an indexed field is selective, which means fast, when it returns a small share of the records. A leading wildcard (LIKE '%abc') or a filter such as != null on a huge object cannot use an index and may fail with "Non-selective query against large object type" in triggers. Filter on indexed fields and keep results small.

SOSL in a sentence

When you need to search text across many objects at once (like a search box), Apex also has SOSL: FIND 'Acme' IN ALL FIELDS RETURNING Account(Name), Contact(LastName). Use SOQL when you know the object and want filtering; use SOSL for keyword search.

Common mistakes

  • Putting OR and AND together without parentheses.
  • Quoting a date literal ('2026-01-01' is a string, not a date).
  • OFFSET beyond 2,000.
  • A filter on a non-indexed field in a large org.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which clause returns the five largest accounts by revenue?

    Descending order puts the largest first; LIMIT 5 keeps five. LIMIT comes after ORDER BY.
  2. 2. What does LIKE 'Ac_e%' match?

    _ matches exactly one character and % matches any run of characters.
  3. 3. How do you filter records created in the last 30 days?

    LAST_N_DAYS:30 is a built-in date literal.
  4. 4. What is the maximum useful OFFSET value?

    OFFSET is capped at 2,000 rows.

Summary

  • WHERE filters with comparison operators, IN, LIKE and null checks; add parentheses when mixing AND and OR.
  • Date literals such as TODAY and LAST_N_DAYS:30 do the date maths for you.
  • ORDER BY sorts (with NULLS FIRST/LAST); LIMIT and OFFSET page results, with OFFSET capped at 2,000.
  • Selective filters on indexed fields keep queries fast.

Next: Relationship Queries