Salesforce Tutorial# 9: Introduction to SOQL and SOSL

Salesforce Object Query Language (SOQL) is similar to SELECT statement in SQL (Structured Query Language). Using SOQL one can search the organization specific data. SOQL can be used with Apex, Visualforce and Schema explorer of Force.com IDE. While writing query one have to combine SELECT command with a list of fields to retrieve as well as the conditions for selecting rows.

Refer the following general syntax for simple SELECT query in SOQL:-

Example :-

Hints when to use SOQL and SOSL :

Use SOQL when you have some basic prior knowledge about the objects and its data. At other hand when you know which data you want to retrieve from the object. SOQL can be used to retrieve data from a single object or from multiple objects which are related to one another. Using SOQL we can get Count, Sort queries too. SOQL can be used with Salesforce Object Search Language (SOSL) APIs to search your organization’s Salesforce data if you have built your own salesforce custom UI.

SOSL is a text-based search technique which works on programmed based on search index. Programmers used SOSL when they are not aware about in which object or fields the data has. It retrieves data for a specific term that is inside the field or build search index for multiple terms within field. It is used to retrieve multiple objects efficiently though may not related to each other. It is used to retrieve data from multiple divisions too.

To increase the performance of searching use SOSL than SOQL as SOSL is faster than SOQL. If you are searching for specific and single term search then SOQL is best option as SOSL combines multiple terms in the single field. Number of fields in search should be minimum because the large number of fields leads to increase in number of permutations, which can be difficult to tune.

Relationship Queries

Using relationship queries one can retrieve the related object data from the database. There are two types of relationship queries Parent-to- Child and Child-to-Parent. These queries are similar to SQL joins. Relationship queries retrieve some valid relationship path. One can use relationship queries to retrieve object of one type based on criteria applies for other types of object.

To understand the concept of Parent and child relationship refer the following diagram

SOQL

Fig 1 : Relationship between two objects.

Queries traversing from Child to Parent Object

Queries traversing from Parent to Child Object

Aggregate Queries

Salesforce support aggregate functions like SUM () , MAX(), AVG() etc to perform various operations. Any query that includes an aggregate function returns its results in an array of AggregateResult objects. AggregateResult is a read-only Object and is only used for query results.

Aggregate functions are more powerful tool to generate reports when you use them with a GROUP BY clause. Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and incremented per aggregated field.

Semi-Join & Anti-Join

Semi Join and Anti Join are used improve the functionality of relationship queries in SOQ. They are useful in create child-to-child relationships. Sub queries are used with these joins to filter records from one object to another. Anti joins are simply used with NOT. Following are the examples of  Semi join and Anti joins :

Semi Join Example :

Anti join Example:

Field Semi-Join & Anti-Join

When joins are applied on Fields then these are knows as field semi joins or anti joins

Field Semi Join :

Field Anti Join

Reference Semi-Join & Anti-Join

When the joins are applied on reference field then they are known as Reference Semi join or Anti join.

Reference Semi Join

Reference Anti Join

Here the left side operator WhoID is reference field.

Thats it in to introduction of SOQL and SOSL. If you need to refer previous articles, please go through below index.

Tutorial Index

  1. Introduction to Cloud Computing (Salesforce.com and Force.com)
  2. Overview of Database Concepts (Salesforce.com)
  3. Introduction to Force.com
  4. Building Salesforce Custom App and Objects
  5. Object Relationships and Formula Field in Salesforce
  6. Salesforce Security Model and Overview
  7. Automation in Salesforce
  8. Approval Process in Salesforce

Add a Comment

Your email address will not be published. Required fields are marked *