The EXISTS and NOT EXISTS Operators

You can use the unary operators EXISTS and NOT EXISTS with the searchspec argument of the QueryPage operation for any multivalued field of objects supported for Web Services v2.0.

EXISTS and NOT EXISTS only work for multivalued fields and not on other fields, including multi-select picklists. Very few multivalued fields are available, but one example is the address fields on the Account and Contact parent objects. Multivalued fields contain data from multiple records, in the case of the address fields, multiple countries, counties, and so on.

EXISTS returns true if the field expression matches for at least one of the values of the multivalued field.

For example, to query for all contacts where any one of the values of the AlternateAddressExternalSystemId field for all associated addresses of Contact is XYZ:

<Contact searchspec="EXISTS ([AlternateAddressExternalSystemId] = 'XYZ')">

NOT EXISTS returns true if the field expression matches for none of the values of the multivalued field.

For example, to query for all contacts where none of the values of the AlternateAddressExternalSystemId field for all associated addresses of Contact is XYZ:

<Contact searchspec="NOT EXISTS ([AlternateAddressExternalSystemId] = 'XYZ')">

As another example, to query for all accounts where any one of the values of the PrimaryBillToCounty field is Suffolk and any one of the values of the Primary Bill To Street Address field contains Ipswich:

<Account searchspec="EXISTS ([PrimaryBillToCounty] = 'Suffolk' AND 
[PrimaryBillToStreetAddress] LIKE '%Ipswich%')