WHERE conditionExpression | Force.com SOQL and SOSL Reference

`conditionExpression`

The conditionExpression of the WHERE clause uses the following syntax:

1fieldExpression [logicalOperator fieldExpression2 ... ]

You can add multiple field expressions to a condition expression by using logical operators.

The condition expressions in SOSL FIND statements appear in bold in these examples:

FIND {test} RETURNING Account (id WHERE createddate = THIS_FISCAL_QUARTER)
FIND {test} RETURNING Account (id WHERE cf__c includes('AAA'))

You can use parentheses to define the order in which fieldExpressions are evaluated. For example, the following expression is true if fieldExpression1 is true and either fieldExpression2 or fieldExpression3 are true:
1fieldExpression1 AND (fieldExpression2 OR fieldExpression3)
However, the following expression is true if either fieldExpression3 is true or both fieldExpression1 and fieldExpression2 are true.
1(fieldExpression1 AND fieldExpression2) OR fieldExpression3
Client applications must specify parentheses when nesting operators. However, multiple operators of the same type do not need to be nested.

`fieldExpression`

A fieldExpression uses the following syntax:

1fieldName comparisonOperator value

where:

Syntax	Description
fieldName	The name of a field in the specified object. Use of single or double quotes around the name will result in an error. You must have at least read-level permissions to the field. It can be any field except a long text area field, encrypted data field, or base64-encoded field. It does not need to be a field in the `fieldList`.
comparisonOperator	Case-insensitive operators that compare values.
value	A value used to compare with the value in `fieldName`. You must supply a value whose data type matches the field type of the specified field. You must supply a native value—other field names or calculations are not permitted. If quotes are required (for example, they are not for dates and numbers), use single quotes. Double quotes result in an error.

Comparison Operators

The following table lists the comparisonOperator values that are used in fieldExpression syntax. Comparisons on strings are case-insensitive.

Operator	Name	Description
`=`	`Equals`	Expression is true if the value in the specified `fieldName` equals the specified `value` in the expression. String comparisons using the equals operator are case-sensitive for unique case-sensitive fields and case-insensitive for all other fields.
`!=`	`Not equals`	Expression is true if the value in the specified `fieldName` does not equal the specified `value`.
`<`	`Less than`	Expression is true if the value in the specified `fieldName` is less than the specified `value`.
`<=`	`Less or equal`	Expression is true if the value in the specified `fieldName` is less than, or equals, the specified `value`.
`>`	`Greater than`	Expression is true if the value in the specified `fieldName` is greater than the specified `value`.
`>=`	`Greater or equal`	Expression is true if the value in the specified `fieldName` is greater than or equal to the specified `value`.
`LIKE`	`Like`	Expression is true if the value in the specified `fieldName` matches the characters of the text string in the specified `value`. The `LIKE` operator in SOQL and SOSL is similar to the `LIKE` operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards. The `%` and `_` wildcards are supported for the `LIKE` operator. The `%` wildcard matches zero or more characters. The `_` wildcard matches exactly one character. The text string in the specified `value` must be enclosed in single quotes. The `LIKE` operator is supported for string fields only. The `LIKE` operator performs a case-insensitive match, unlike the case-sensitive matching in SQL. The `LIKE` operator in SOQL and SOSL supports escaping of special characters `%` or `_`. Don’t use the backslash character in a search except to escape a special character. For example, the following query matches Appleton, Apple, and Appl, but not Bappl: `1SELECT AccountId, FirstName, lastname 2FROM Contact 3WHERE lastname LIKE 'appl%'`
`IN`	`IN`	If the value equals any one of the specified values in a `WHERE` clause. For example: `1SELECT Name FROM Account 2WHERE BillingState IN ('California', 'New York')` The values for `IN` must be in parentheses. String values must be surrounded by single quotes. `IN` and `NOT IN` can also be used for semi-joins and anti-joins when querying on ID (primary key) or reference (foreign key) fields.
`NOT IN`	`NOT IN`	If the value does not equal any of the specified values in a `WHERE` clause. For example: `1SELECT Name FROM Account 2WHERE BillingState NOT IN ('California', 'New York')` The values for `NOT IN` must be in parentheses, and string values must be surrounded by single quotes. There is also a logical operator `NOT`, which is unrelated to this comparison operator.
`INCLUDES EXCLUDES`		Applies only to multi-select picklists.

Logical Operators

The following table lists the logical operator values that are used in fieldExpression syntax:

Operator	Syntax	Description
`AND`	`fieldExpressionX AND fieldExpressionY`	`true` if both `fieldExpressionX` and `fieldExpressionY` are true.
`OR`	`fieldExpressionX OR fieldExpressionY`	`true` if either `fieldExpressionX` or `fieldExpressionY` is true. Relationship queries with foreign key values in an `OR` clause behave differently depending on the version of the API. In a `WHERE` clause that uses `OR`, if the foreign key value in a record is null, the record is returned in version 13.0 and later, but not returned in versions before 13.0. `1SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'` The contact with no parent account has a last name that meets the criteria, so it is returned in version 13.0 and later.
`NOT`	`not fieldExpressionX`	`true` if `fieldExpressionX` is false. There is also a comparison operator `NOT IN`, which is different from this logical operator.

Quoted String Escape Sequences

You can use the following escape sequences with SOSL:

Sequence	Meaning
`\n` or `\N`	New line
`\r` or `\R`	Carriage return
`\t` or `\T`	Tab
`\b` or `\B`	Bell
`\f` or `\F`	Form feed
`\"`	One double-quote character
`\'`	One single-quote character
`\\`	Backslash
LIKE expression only: `\_`	Matches a single underscore character ( _ )
LIKE expression only:`\%`	Matches a single percent sign character ( % )

If you use a backslash character in any other context, an error occurs.

Example WHERE Clauses

Example(s)
`1FIND {test} 2 RETURNING Account (id WHERE createddate = THIS_FISCAL_QUARTER)`
`1FIND {test} 2 RETURNING Account (id WHERE cf__c includes('AAA'))`
`1FIND {test} 2 RETURNING Account (id), User(Field1,Field2 WHERE Field1 = 'test' order by id ASC, Name DESC)`
`1FIND {test} IN ALL FIELDS 2 RETURNING Contact(Salutation, FirstName, LastName, AccountId WHERE Name = 'test'), 3 User(FirstName, LastName), 4 Account(id WHERE BillingState IN ('California', 'New York'))`
`1FIND {test} 2 RETURNING Account (id WHERE (Name = 'New Account') 3 or (Id = '001z00000008Vq7' 4 and Name = 'Account Insert Test') 5 or (NumberOfEmployees < 100 or NumberOfEmployees = null) 6 ORDER BY NumberOfEmployees)`
To search for a Salesforce Knowledge article by ID: `1FIND {tourism} 2 RETURNING KnowledgeArticleVersion (Id, Title WHERE id = 'ka0D0000000025eIAA')`
To search for multiple Salesforce Knowledge articles by ID: `1FIND {tourism} 2 RETURNING KnowledgeArticleVersion 3 (Id, Title WHERE id IN ('ka0D0000000025eIAA', 'ka0D000000002HCIAY'))`
To search for "San Francisco" in all fields of all `My_Custom_Object__c` objects that have a geolocation or address location within 500 miles of the latitude and longitude coordinates 37 and 122, respectively: `1FIND {San Francisco} 2 RETURNING My_Custom_Object__c (Id 3 WHERE DISTANCE(My_Location_Field__c,GEOLOCATION(37,122),'mi') < 100)`