Salesforce Tutorial# 9: Introduction to SOQL and SOSL

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:-

Example:-

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

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 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 & 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 :

Anti join Example:

Field Semi-Join & Anti-Join

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

Field Semi Join :

Field Anti Join

Reference Semi-Join & 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

Reference Anti Join

Here the left side operator WhoID is the reference field.

That’s it into introduction of SOQL and SOSL. If you need to refer to previous articles, please go through the 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
  9. Introduction to SOQL and SOSL
  10. Introduction to Apex
  11. Salesforce Data Management
  12. Visualforce MVC Architecture on Cloud
  13. Salesforce Reports and Dashboards
  14. Building a Visualforce (Custom) Page for the Salesforce App
  15. Salesforce Sandbox and Overview of Force.com capabilities
  16. Learning Apex and Deployment Tools

 

One Comment
  1. December 19, 2017 | Reply

Add a Comment

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