SELECT

The conditionExpression, the WHERE clause, uses the following syntax:

fieldExpression   logicalOperator   fieldExpression2 ?

Note

The WHERE clause behaves in two different ways, depending on the version, when handling null values in a parent field for a relationship query. In a WHERE clause that checks for a value in a parent field, if the parent does not exist, the record is returned in Version 13.0 and later, but not returned in versions before 13.0.

SELECT Id FROM Case WHERE Contact.Lastname = null

Case record Id values are returned in version 13.0 and later, but are not returned in versions before 13.0. For more information, see Lookup Relationships and Outer Joins.

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

fieldExpression uses the following syntax:

fieldName   comparisonOperator   value

where:

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

SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE ‘appl_%’

IN

NOT IN

INCLUDES
EXCLUDES

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

SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'

You can use the following escape sequences with SOQL:

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

Reserved characters, if specified in aSELECT clause as a literal string (between single quotes), must be escaped (preceded by the backslash \ character) in order to be properly interpreted. An error occurs if you do not precede reserved characters with a backslash.

' (single quote)
\ (backslash)

For example, to query the AccountName field for "Bob's BBQ," use the following SELECT statement:

SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'

In order to discover the number of rows that are returned by a query, use count() in a SELECT clause:

SELECT count() FROM objectType [WHERE conditionExpression][LIMIT   number_of_rows] 

For example:

     SELECT count() FROM Account WHERE Name LIKE ’a%’

     SELECT count() FROM Contact, Contact.Account WHERE Account.name = ’MyriadPubs’

count() must be alone in the SELECT statement, it cannot be mingled with other elements in the SELECT clause. count() cannot be used with ORDER BY, however, it can be used with LIMIT.

The query result Size field returns the number of rows. The records themselves are returned as null.

You can use ORDER BY in a SELECT statement:

SELECT fieldList FROM  objectType [WHERE conditionExpression] 
[ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ? ]
[LIMIT number_of_rows] 

For example, the following query returns a query result with Account records in alphabetical order by first name, sorted in descending order, with accounts that have null names appearing last:

SELECT Name FROM Account ORDER BY Name DESC NULLS LAST

The following factors affect results returned with ORDER BY:

The following limitations apply to data types when using ORDER BY:

You can use ORDER BY with the optional LIMIT qualifier, in a SELECT statement:

     SELECT Name FROM Account WHERE industry = ’media’
          ORDER BY BillingPostalCode ASC NULLS last LIMIT 125

You are limited to 32 fields in an ORDER BY query. If you exceed the limit, a malformed query fault is returned.

Use LIMIT to specify the maximum number of rows to return:

     SELECT fieldList FROM objectType [WHERE conditionExpression] 
       LIMIT number_of_rows   

For example:

     SELECT Name FROM Account WHERE industry = ’media’ LIMIT 125

This query returns the first 125 records whose industry is Media.

You can use LIMIT with count() as the fieldList to count up to the maximum specified.