sforce Object Query Language (SOQL)

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 ORDERBY 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 sforce object that you want to query. You must specify a valid sforce object and must have read-level permissions to that object. For a list of valid objects, see List of sforce 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.

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

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	One of the comparison operators listed in Comparison 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

A fieldExpression uses the following comparisonOperators:


Operator	Name	Description
=	Equals	Expression is True if the value in the specified fieldName equals the specified value in the expression.
!=	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 Field Type). 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.

Date Formats

A fieldExpression uses the following date formats (milliseconds and time zone are optional):


Use	Format Syntax	Example
Date only	`YYYY-MM-DD`	`1999-01-01`
Date and time	`YYYY-MM-DDThh:mm:ss`	`1999-01-01T24:01:01`
Date, time, and milliseconds	`YYYY-MM-DDThh:mm:ss.MILLIS`	`1999-01-01T24:01:01.001`
Date, time, milliseconds, and time zone offset	`YYYY-MM-DDThh:mm:ss.MILLISZ+hh:mm` `YYYY-MM-DDThh:mm:ss.MILLISZ-hh:mm` `YYYY-MM-DDThh:mm:ss.MILLISZ`	`1999-01-01T24:01:01.001Z+01:00` `1999-01-01T24:01:01.001Z-01:00` `1999-01-01T24:01:01.001Z`

:: 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 > 2003-10-29T11:30:00Z

Logical Operators

A logicalOperator is used to join two or more fieldExpressions. A logicalOperator is one of the following values:


Operator	Syntax	Description
`and`	fieldExpressionX `and` fieldExpressionY	True only if both fieldExpressionX and fieldExpressionY are True.
`or`	fieldExpressionX `or` fieldExpressionY	True if either fieldExpressionX and fieldExpressionY is True.
`not`	fieldExpressionX `or` fieldExpressionY	True if fieldExpressionX is True fieldExpressionY is False.

Changing the Batch Size in Queries

By default, the batch size for the number of records returned in a query or queryMore call is set to 2000. 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 following sample Java (Axis) code demonstrates setting the batch size to three (3) records.

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

The following sample C# (.NET) code demonstrates setting the batch size to three (3) records.

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