sforce logo

sforce Object Query Language (SOQL)


You 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:

:: 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 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; 

Copyright 2000-2003 SalesForce.com, Inc.
All rights reserved Various trademarks held by their respective owners.