Query Language
Force.com has two query languages. One is called Salesforce Object Query Language (SOQL) and is used for structured queries. The other language, Salesforce Object Search Language (SOSL), is used for searching the full text of one or more objects.
SOQL
Don’t let the name confuse you. Despite some similarities in syntax, SOQL is very different from SQL. It has more in common with a reporting or object-traversal language than its more mathematically grounded ancestor.
Listing 2.3 shows a sample SOQL query on a custom object. It returns the names, statuses, and expected revenue amounts for the top-ten largest uninvoiced projects started in the last quarter, in descending order by pending revenue.
SELECT Name, Total_Billable_Revenue_Pending_Invoice__c, Project_Status__c
FROM Proj__c
WHERE Invoiced__c = FALSE and Start_Date__c = LAST_QUARTER
ORDER BY Total_Billable_Revenue_Pending_Invoice__c DESC LIMIT 10
The query specifies a list of columns to be returned (SELECT), the object to query (FROM), filter conditions (WHERE), sorting results (ORDER BY) in descending (DESC) order, and a hard limit on the maximum number of rows to return (LIMIT).
Selecting a single object is the simplest type of SOQL query. More advanced queries select fields from multiple related objects, nested resultsets from child objects using subqueries, and perform semi-joins and anti-joins using IN and NOT IN.
The following subsections describe the four most significant differences between SQL and SOQL.
Implicit Join
In SQL, you can join any table with any other table, typically with one or more Boolean expressions involving pairs of columns. Assuming that the data types of the columns in the join expression are comparable, the join query returns the corresponding rows of both tables as specified in your join expression.
In Force.com, data from multiple standard and custom objects can be combined, but only in ways predetermined by you when you designed your database. SOQL itself does not support any concept of joins, other than semi-join and anti-join. Using SOQL, you tell the Force.com platform which fields of which objects to retrieve, and the platform does the work of traversing the data, maintaining the integrity between objects in accordance with the relationships you defined.
This behavior has its pros and cons. You cannot perform truly ad hoc queries, in which data from multiple objects is combined in ways possibly unanticipated by the database designer. But it results in much simpler, more concise queries that can be optimized entirely by the platform.
Nested Resultsets
In SQL, querying two tables in a one-to-many relationship without aggregate functions and GROUP BY results in a cross product of the rows. For example, assume you have a table containing orders and another table with their line items, and issue the query in Listing 2.4.
Listing 2.4 Relationship Query in SQL
SELECT Orders.OrderId, OrderLineItems.LineItemId
FROM Orders, OrderLineItems
WHERE Orders.OrderId = OrderLineItems.OrderId
Assume that there are two orders (1 and 2), each with three line items (1–3 and 4–6). Table 2.1 shows the results of executing the query.
To begin comparing this with Force.com, Listing 2.5 shows an equivalent query in SOQL.
Listing 2.5 Relationship Query in SOQL
SELECT OrderId, (SELECT LineItemId FROM OrderLineItems)
FROM Orders
Note the lack of a WHERE clause to perform the join and the use of a subquery to nest the line items. Force.com is aware of the parent-child relationship between Orders and OrderLineItems, so it performs the join automatically. The result can be visualized as arrays of nested records, as shown in Figure 2.1. The outer record is the order, and each order contains an array of line items.
No Functions in Column List
You might have included functions like LEFT, RIGHT, MID, LEN, and IFF along with your columns in a SQL SELECT statement. SOQL does not permit functions in the SELECT list. The only exceptions are built-in aggregate functions such as COUNT, which returns the number of records in the query. But aggregate functions can’t be used in a query containing any other fields in the SELECT list.
Governor Limits
Force.com prevents a single user from consuming more than its fair share of system resources. This ensures a consistent level of system performance for all tenants. Limitations placed on resource consumption are called governor limits. A few examples of governor limits are the number of records that can be queried at one time, the amount of memory used by your code, and the size of messages sent between Force.com and external hosts. Some governor limits vary based on the type of licensing agreement you have in place with Salesforce.
SOSL
SOSL provides full-text search capabilities across many objects and fields simultaneously. This is an always inefficient and often impossible task in SOQL. SOSL statements can perform a search over all records, or incorporate SOQL to narrow the search scope and achieve the best of both worlds: structured and unstructured search. The SOSL expression in Listing 2.6 returns the IDs of records in four custom objects that begin with the word java in any of their fields.
FIND 'java*' IN ALL FIELDS
RETURNING Project__c, Resource__c, Assignment__c, Skill__c