SOQL

SALESFORCE OBJECT QUERY LANGUAGE

Introduction to Salesforce.com Object Query Language (SOQL)

I'll assume for a moment that you are familiar with basic SQL for the purposes of this tutorial. If you are not, there are plenty of great resources on the web to brush up with. As a developer looking to extend Salesforce.com, SOQL is a pretty important and powerful aspect of coding. You can use SOQL to build your own custom query stings. These query strings can be used in the following places:

1. Apex statements
2. Visualforce getter methods and controllers
3. In the queryString param passed in the query() call
4. Finally, you can use the Schema Explorer in the Eclipse Toolkit (this one is pretty slick)

For those of you familar with SQL, you will find some differences but for the most part SOQL does just about everything you'll need it to do.

SOQL uses the "SELECT" statement combined with any filter statements to bring back sets of data. The data sets returned may be optionally ordered as well (just like in SQL). Here is a basic example:

SELECT field1, field2, field3

FROM an object

WHERE filter statement(s) and (optionally) order the results

So, if you we want to get all the Leads from  your Salesforce.com account where the email address equals = "john.doe@somecompany.com" you would use the following SOQL statement:

SELECT ID, Name from Lead WHERE email = 'john.doe@somecompany.com'

 For a detailed documentation on the syntax of a SOQL SELECT statement, go to thisSalesforce.com developer documentation page.

SOQL - COUNT()

Getting the "Count" of results being returned in a SOQL data set is pretty simple as well. For example, if I wanted to know how many Leads were going to be returned in my SELECT statement above, I can use the COUNT() function below:

SELECT COUNT() from Lead WHERE email = 'john.doe@somecompany.com'

SOQL Comparison Operators

Operator	Common name
=	Equals
!=	Not equals
<	Less than
<=	Less than or equal
>	Greater than
>=	Greater than or equal
IN	In
NOT IN	Not in (WHERE clause)
INCLUDES EXCLUDES	Applies to multi-select picklists
LIKE	Like (see section below)

For a full chart of all the comparison operators, check out this Salesforce.com developer documentation page.

SOQL - Like Operator

The LIKE operator provides a way to match partial text strings and includes support for wildcards. Let's say for a moment we want to find all the Leads where the email domain is the same. For this, we can use a "LIKE" operator.  He is an example of a LIKE statement with the % wildcard.

SELECT Id, Name from Lead WHERE email  LIKE '%somecompany.com'

 The placement of the percent sign '%' is key here. I am basically saying, bring me back all the Leads where the email ends with "somecompany.com". Therefore I place the '%' at the beginning of whatever I am looking for." Anything to the left of the % sign is ignored in the search. If I didn't know the full domain I could use the following statement:

SELECT Id, Name from Lead WHERE email  LIKE '%somecomp%'

This is going to return all the leads where the email contains "somecomp".  

Other wildcard is the underscore "_". Thing is used to match exactly one character.

Note: Unlike with SQL, the LIKE operator in SOQL performs a case-insensitive match.

SOQL - WHERE/OR

 If you want to extend the WHERE clause to include multiple values, you can OR. See the example statement below:

SELECT ProductCode FROM PricebookEntry WHERE CurrencyIsoCode = 'USD' or CurrencyIsoCode = 'GBP'

Taking it a step further, you can evaludate multiple things in the WHERE clause:

SELECT ProductCode,UnitPrice FROM PricebookEntry 
WHERE (UnitPrice >= 10 and CurrencyIsoCode='USD') 
OR (UnitPrice >= 5.47 and CurrencyIsoCode='EUR')