Skip to content
Suggest a correction

Compare two queries examples

Example3 min read

Use these examples to build Record Health Checks with the Compare two queries Evaluation Type. A Check of this type runs two SOQL queries and compares their results: two numbers, such as covered and total Opportunities, or two lists, such as Opportunity owners and Account Team members. Choose it when both sides of the comparison come from related records.

Each example is a set of instructions; the package does not install these Checks. You create each one in Setup, in Custom Metadata Types → Record Health Check → Manage Records.

ExampleQuestion it answersWhat it teaches
Check that every open Opportunity has a Contact RoleDo all open Opportunities have a Contact Role?Two counts compared with Equal to, an aggregate alias, and a count-query applicability rule
Check that open pipeline includes a previously purchased ProductDoes open pipeline share a Product with past purchases?Two lists compared with Lists overlap
Check that the Account Team includes every open Opportunity ownerIs every open Opportunity owner on the Account Team?Two lists compared with Lists contain all

Fields every Compare two queries Check uses

Section titled “Fields every Compare two queries Check uses”
FieldWhat to enter
Source QueryThe first query. Its result is shown as Found.
Source Query FieldThe field or aggregate alias to read from the first query. Leave blank for a bare COUNT().
Comparison QueryThe second query. Its result is shown as Expected.
Comparison Query FieldThe field or aggregate alias to read from the second query. Leave blank for a bare COUNT().
How To Read Query ResultsOne row or aggregate to compare two values, or Compare as lists to compare two lists.
Comparison OperatorFor two values: Equal to, Not equal to, Greater than, At least, Less than, At most, Contains text, or Does not contain text. For two lists: Lists overlap, Lists contain all, or Lists match exactly.
If Query Finds No RecordsWhat to report when a query returns no rows. Required with Compare as lists.

Leave Expected Value Comes From blank; the Comparison Query is the expected side.

  • A bare SELECT COUNT() always returns a number, even 0.
  • An aggregate such as COUNT_DISTINCT(field), COUNT(field), or SUM(field) returns no rows when nothing matches. If Query Finds No Records then decides the result; when it is blank, the Check is skipped.
  • With Compare as lists, an empty list on either side uses If Query Finds No Records.

The list operators pass in these cases:

OperatorPasses when
Lists overlapAt least one value appears in both lists.
Lists contain allThe Comparison Query list contains every value in the Source Query list. It can contain others.
Lists match exactlyBoth lists contain the same values, each the same number of times.

List comparisons ignore capitalization. They also ignore repeated values, except Lists match exactly, which counts them.

Know when to choose another Evaluation Type

Section titled “Know when to choose another Evaluation Type”

Use a Query example when one side is a fixed value or a value from the open record. Use an Apex example when the comparison needs custom logic.