Sforce Object Query Language (SOQL)

---

Use the Sforce Object Query Language (SOQL) to construct simple but powerful query strings for the queryString parameter in the query call. Similar to the SELECT command in SQL, SOQL allows you to specify the source object (such as Account), a list of fields to retrieve, and conditions for selecting rows in the source object. This topic includes:

SOQL Syntax
conditionExpression Syntax
fieldExpression Syntax
Logical Operators
Changing the Batch Size in Queries
Querying Multi-Select Picklists
Filtering on Boolean Fields

:: Note

SOQL does not support all advanced features of the SQL SELECT command. For example, you cannot use SOQL to perform join operations, use wildcards in field lists, use calculation expressions, or specify an ORDER BY clause to sort rows in the result set.

SOQL Syntax

SOQL uses the following syntax:

select fieldList from objectType [where conditionExpression] 

where:

Syntax	Description
fieldList	Specifies a list of one or more fields, separated by commas, that you want to retrieve from the specified object.   You must specify valid field names and must have read-level permissions to each specified field. The fieldList defines the ordering of fields in the query results.
objectType	Specifies the type of object that you want to query. You must specify a valid object and must have read-level permissions to that object. For a list of valid objects, see List of Standard Objects.
conditionExpression	Determines which rows in the specified object to retrieve. If unspecified, the query retrieves all rows in the object. See conditionExpression Syntax for the appropriate syntax.

:: NOTE

SOQL statements cannot exceed 10,000 characters. For SOQL statements that exceed this maximum length, the Web service returns an ExceptionCode of MALFORMED_QUERY; no result rows are returned.

conditionExpression Syntax

The conditionExpression uses the following syntax:

fieldExpression [logicalOperator fieldExpression2][logicalOperator 
fieldExpression3]... 

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.
fieldExpression1 AND (fieldExpression2 OR fieldExpression3)
However, the following expression is True if either fieldExpression3 is True or both fieldExpression1 and fieldExpression2 are True.
(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.

See fieldExpression Syntax for the syntax of fieldExpressions. See Logical Operators for the valid logical operators.

fieldExpression Syntax

A fieldExpression uses the following syntax:

fieldName 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-it does not need to be a field in the fieldList.
comparisonOperator	See Comparison Operators for a list of valid operators.
value	A value, enclosed in single quotes (double quotes result in an error), 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. For date values, use the formatting listed in Date Formats.

Comparison Operators

The following table lists the comparisonOperator values that are used in fieldExpression syntax. Note that 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-insensitive.
!=	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 is similar to the same 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 (see string). The like operator performs a case-insensitive match, unlike the case-sensitive matching in SQL. The like operator in SOQL does not currently support escaping of special characters such as % or _. The \ (backslash) character should not be used. select AccountId, FirstName, lastname from Contact where lastname like `%appl_%' matches Appleton, Apple, Bapple, but not Appl.
includes excludes		Applies only to multi-select picklists. See Querying Multi-Select Picklists.

Date Formats

A fieldExpression uses the following date formats:

Use	Format Syntax	Example
Date only	YYYY-MM-DD	1999-01-01
Date, time, and time zone offset	YYYY-MM-DDThh:mm:ss+hh:mm YYYY-MM-DDThh:mm:ss-hh:mm YYYY-MM-DDThh:mm:ssZ	1999-01-01T23:01:01+01:00 1999-01-01T23:01:01-08:00 1999-01-01T23:01:01Z

The zone offset is always from UTC. For more information, see:

http://www.w3.org/TR/xmlschema-2/#isoformats
http://www.w3.org/TR/NOTE-datetime

:: Note

For a fieldExpression that uses date formats, the date is not enclosed in single quotes. No quotes should be used around the date. For example:

select Id from Account where CreatedDate > 2005-10-08T15:00:00Z

Logical Operators

The following table lists the logicalOperator 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.
not	not fieldExpressionX	True if fieldExpressionX is False.

Changing the Batch Size in Queries

By default, the number of rows returned in the query result object (batch size) returned in a query or queryMore call is set to 500. Client applications can change this setting by specifying the batch size in the QueryOptions portion of the SOAP header before invoking the query call. The maximum batch size is 2,000 records, however the maximum is automatically reduced if you select large text fields or standard objects like Account. In such cases, the actual batch size may be reduced by a factor of two per large text field or standard object selected. If you select many fields, the maximum will also be reduced to maintain performance. Similarly, even if a batch size of 200 is selected, if the API determines that its more efficient to return more than 200, it will. There is no guarantee that the requested batch size will be the actual batch size. This is done to maximize performance.

:: Note

The batch size will be no more than 200 if the SOQL statement selects two or more custom fields of type long text. This is to prevent large SOAP messages from being returned.

The following sample Java (Axis) code demonstrates setting the batch size to two hundred and fifty (250) records.

 
QueryOptions qo = new QueryOptions(); 
qo.setBatchSize(new Integer(250)); 
binding.setHeader(new SforceServiceLocator().getServiceName().getNamespaceURI(), 
"QueryOptions", qo);         
 

The following sample C# (.NET) code demonstrates setting the batch size to two hundred and fifty (250) records.

 
binding.QueryOptionsValue = new QueryOptions(); 
binding.QueryOptionsValue.batchSize = 250; 
binding.QueryOptionsValue.batchSizeSpecified = true; 
 

Querying Multi-Select Picklists

Client applications use a specific syntax for querying multi-select picklists (in which multiple items can be selected).

Supported Operators

The following operators are supported for querying multi-select picklists:

Operator	Description
=	Equals the specified string.
!=	Does not equal the specified string.
includes	Includes (contains) the specified string.
excludes	Excludes (does not contain) the specified string.

Semicolon Character

A semicolon is used as a special character to specify AND. For example, the following notation:

'AAA;BBB' 

means 'AAA' and 'BBB'. Specifying AND is used for multiselect picklists, for cases when two or more items must be selected.

Examples

In the following example SOQL notation:

MSP1__c = 'AAA;BBB' 

the query filters on values in the MSP1__c field that are equal to AAA and BBB selected (exact match).

In the following example SOQL notation:

MSP1__c includes ('AAA;BBB', 'CCC') 

the query filters on values in the MSP1__c field that contains either of these values:

AAA and BBB selected
CCC selected.

A match will result on any field value that contains 'AAA' and 'BBB' or any field that contains 'CCC'. For example, the following will be matched:

matches with 'AAA;BBB':

    'AAA;BBB' 
    'AAA;BBB;DDD' 

matches with 'CCC':

    'CCC' 
    'CCC;EEE' 
    'AAA;CCC' 
    'BBB;CCC' 

Filtering on Boolean Fields

To filter on a Boolean field, use the following syntax:

where BooleanField = TRUE  
where BooleanField = FALSE