Salesforce Object Query Language (SOQL) is used to build queries for fetching data in the Force.com platform. Just as we write a query in Structured Query Language (SQL) with some columns and a table, here, in SOQL, we write a query with some fields and an object. However, SOQL does not support all the features of SQL. For example, the * character in the SELECT statement denotes all columns in a table in SQL, but it cannot be used in the SELECT statement in SOQL. So, to retrieve all fields in SOQL, we have to mention all the fields separated by commas.

SOQL is case insensitive. For ease of use, we suggest you to maintain SOQL keywords in uppercase and fields in initial case (first letter in uppercase and the rest in lowercase). Throughout this book, all SOQL keywords will be written in uppercase and object names, field lists, conditions, and so on will be written in lowercase.

SOQL is very easy to understand if you have prior knowledge in SQL. As mentioned earlier, however, it does not support all the features available in SQL. If we think of tables as objects and columns as fields in Salesforce, writing SOQL becomes easier. Salesforce has standard objects (objects defined by Salesforce) and custom objects (objects defined by the user). The custom object ends with __c for identification purposes.

Good knowledge of SOQL helps us to optimize our code. If we are looking for data from different objects, SOQL helps us a lot in accomplishing that. Instead of writing complex code to achieve this, an administrator or developer with vast knowledge of SOQL may easily accomplish these kinds of tasks. The functions available in SOQL reduce our workload and save time.

The sample queries used in this book are real-time examples with step-by-step explanations. Beginners will gain confidence as we go ahead. Administrators and developers can also get ideas on how to optimize their code for faster execution of queries. An administrator can easily build any kind of complex report in an Excel file by extracting data from the objects using SOQL and delivering it to the clients in a timely manner if he or she has good knowledge of SOQL. SOQL eases the tasks of administrators, who are always looking for data.

A developer also faces many situations where they may have to write SOQL queries in Apex programming. If the developer has wide knowledge of SOQL, they can easily accomplish their task without reiterating again and again to form data for manipulation.

Make use of the tools available at Salesforce.com to execute the query instantly to clarify any doubts that arise. Salesforce provides tools, and third-party tools are also available. Steps with installation procedures and guidelines are available in Chapter 6, Tools with Installation Guidelines. The Developer Console can also be used for the easier and instant execution of queries.

To use SOQL, we need to know the API name of the objects. To know the API names of the standard objects in Salesforce, visit the following reference link provided by Salesforce:

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_list.htm

Since custom objects are user-defined objects, information about these objects will not be available under Customize in the Force.com setup.

The steps to get the API name of the custom objects change from environment to environment. In my organization, it is Setup | Build | Create | Objects, as shown in the following screenshot. We can view an object's API name on selecting it.

The Objects link displays all the custom objects available in our organization, as shown in the following screenshot:

The Employee link redirects us to the Employee object custom definition, as shown in the following screenshot:

The SELECT statement is used to retrieve data from objects. Relationships must exist among objects in case we want to retrieve data from two or more objects.

Relationship queries (queries for fetching records from more than one object) will be discussed in Chapter 2, Basic SOQL Queries.

The main purpose of SOQL is to fetch data from Salesforce objects. SOQL can be used in the following places:

Similar to SQL, SOQL also makes use of the SELECT statement to fetch data. Let us explore the following SOQL syntax:

SELECT fields 
FROM Object 
WHERE Condition 
Ordering LIMIT
FOR VIEW Or FOR REFERENCE
OFFSET 
UPDATE VIEWSTAT 

The preceding query is explained as follows:

SELECT, fieldList, FROM, and Object are required. The others are optional in SOQL.

We should use the API names of the fields in the SELECT statement. We should not use the labels of the fields. The API names are available in the object definition. For Standard Fields, the Field Name column refers to the API name, and for Custom Fields, the API Name column refers to the API name.

To get the API names of standard objects in Salesforce, navigate to Setup | Build | Customize | Object | Fields.

Let us see how to get the API names of the Account object fields. To get the API names of the Account object fields, navigate to Setup | Build | Customize | Accounts | Fields as shown in the following screenshot. The Standard object fields are present under Customize and custom objects are present under Create | Objects in Salesforce.

In the Account Standard Fields section, the Field Name column refers to the API name of the standard fields, as shown in the following screenshot:

In the Account Custom Fields & Relationships section, the API Name column denotes the API name of the fields, as shown in the following screenshot:

To get the API names of custom objects in Salesforce, navigate to Setup | Build | Create | Objects, as shown in the following screenshot, and select the object:

This Objects link displays all the custom objects available in our organization, as shown in the following screenshot:

The Field Name column in the Standard Fields section denotes the API names of the fields, as shown in the following screenshot:

The API Name column in the Custom Fields & Relationships section denotes the API names of the fields, as shown in the following screenshot:

Before getting started with writing our first SOQL statement, we have to install a software to execute our queries. Salesforce offers a couple of tools to write and execute SOQL queries instantly. Salesforce also supports other third-party tools to write and execute queries. Let us write a simple SOQL query to fetch the IDs and names of accounts from the Account object.

A sample query is given as follows:

SELECT Id, Name FROM Account

Refer to the following screenshot:

Here, Id and Name are standard fields of the Account object.

Let us see another example of how to fetch custom fields in standard objects. Refer to the following screenshot:

Here, Id and Name are standard fields and Active__c and CustomerPriority__c are custom fields.

A sample query is given as follows:

SELECT Id, Name, Active__c, CustomerPriority__c FROM Account

In the preceding example, we saw how to retrieve records from Account (standard object). Let us write a simple SOQL query to fetch records from a custom object. In this example, let us make use of a custom object, Employee__c, which has custom fields such as Employee_Name__c, State__c, City__c, and so on.

A sample query is given as follows:

SELECT Name, Employee_Name__c, State__c, City__c FROM Employee__c

Refer to the following screenshot:

Here, Employee__c is a custom object; Name is a standard field; and Employee_Name__c, State__c, and City__c are custom fields.

Each and every object in Salesforce has system fields. System fields are read-only fields. The following is a list of system fields:

The following table describes field names:

Let us see a sample SOQL query to fetch the system fields:

SELECT Id, Name, CreatedDate, LastModifiedDate FROM Account

Refer to the following screenshot:

Here, CreatedDate and LastModifiedDate are system fields.

Let us see another example to fetch FirstName and LastName from the User object.

A sample query is given as follows:

SELECT FirstName, LastName FROM User

Refer to the following screenshot:

Let us see another example to fetch Name and StageName from the Opportunity object.

A sample query is given as follows:

SELECT Name, StageName FROM Opportunity

Refer to the following screenshot:

Let us see another example to fetch Name and Status from the Lead object.

A sample query is given as follows:

SELECT Name, Status FROM Lead

Refer to the following screenshot:

Let us see another example to fetch Id and Name from the Product object. The API name of the Product object is Product2.

A sample query is given as follows:

SELECT Id, Name FROM Product2

Refer to the following screenshot:

Let us see another example to fetch Id and Name from the Price Book object. The API name of the Price Book object is Pricebook2.

A sample query is given as follows:

SELECT Id, Name FROM Pricebook2

Refer to the following screenshot:

Let us see another example to fetch ProductCode, Product2Id, Name, and UseStandardPrice from the Price Book Entry object. The API name of the Price Book Entry object in Salesforce is PricebookEntry.

A sample query is given as follows:

SELECT ProductCode, Product2Id, Name, UseStandardPrice FROM PricebookEntry

Refer to the following screenshot:

In this chapter, we learned what SOQL is and got to know its purpose. While discussing the purpose, we saw where exactly we use SOQL statements in Salesforce development and administration.

We discussed the fetching of the API name of the custom object with detailed descriptions and steps. Moreover, we saw the steps for fetching API names of the standard and the custom fields. The usage of system fields and querying system fields, with a description of each system field, was provided in a table.

Basic syntax of SOQL statements with all reserved keywords was discussed. We also saw some examples for fetching records using the SOQL queries from standard objects and custom objects. Finally, methods to find the difference between custom objects and standard objects and custom fields and standard fields were introduced.