Salesforce Tutorial# 9: Introduction to SOQL and SOSL

Renuka

7 years ago

Salesforce Object Query Language (SOQL) is similar to the 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 has 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:-

SELECT one or list of fields

FROM an Object

WHERE (optional block) filtering criteria

Example:-

SELCET EmpID, Name

FROM Employee

WHERE DEPT_NAME = âRxDâ;

Hints when to use SOQL and SOSL :

Use SOQL when you have some basic prior knowledge about the objects and their data. On the 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 that works on programmed based on the search index. Programmers used SOSL when they are not aware of which object or fields the data has. It retrieves data for a specific term that is inside the field or builds a search index for multiple terms within the 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 the best option as SOSL combines multiple terms in the single field. The number of fields in search should be minimal because a large number of fields lead to an increase in the 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 objects of one type based on criteria applies for other types of objects.

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

Fig 1 : Relationship between two objects.

Queries traversing from Child to Parent Object

For(College_c c:[SELECT Name,University_r.Name, University_c FROM Colllege_c]}
{
  System.debug(âCollege Name:â + c.Name+Â Â Â  âUniversity Name:â+c.University_r.Name);
}

Queries traversing from Parent to Child Object

For (University_c u:[Select Name, (Select Name from Colleges_r) from University_c]}
{
  For(college_c:u.Colleges r)
  {

     System.debug(âUniversity Name :â+u.Name+ âCollege Name:â+c.Name);
  } 
}

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 a 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 x Anti-Join

Semi Join and Anti Join are used to improve the functionality of relationship queries in SOQ. They are useful in creating child-to-child relationships. Subqueries 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 :

Select EmpID, EmpName from Employee WHERE EmpID IN ( Select ProjectID from Project WHERE Status = âBENCHâ)

Anti join Example:

Select EmpID, EmpName from Employee WHERE EmpID NOT IN ( Select ProjectID from Project WHERE Status = âBENCHâ)

Field Semi-Join x Anti-Join

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

Field Semi Join :

SELECT T_ID, T_Name
From Teacher
WHERE T_ID IN (Select T_ID From Department WHERE DEPTNAME = âCOMPUTER SCIENCEâ )

Field Anti Join

SELECT T_ID, T_Name
From Teacher
WHERE T_ID Â NOT IN (Select T_ID From Department WHERE DEPTNAME = âCOMPUTER SCIENCEâ )

Reference Semi-Join x Anti-Join

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

Reference Semi Join

SELECT T_ID FROM TEACHER
WHERE WhoID IN (SELECT FROM DEPARTMENT WHERE DEPT_NAME =âComp Scienceâ)

Reference Anti Join

SELECT T_ID FROM TEACHER WHERE WhoID NOT Â IN (SELECT FROM DEPARTMENT WHERE DEPT_NAME =âComp Scienceâ)

Here the left side operator WhoID is the reference field.

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