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:-
|
1 2 3 4 5 |
SELECT one or list of fields FROM an Object WHERE (optional block) filtering criteria |
Example:-
|
1 2 3 4 5 |
SELCET EmpID, Name FROM Employee WHERE DEPT_NAME = ‘R&D’; |
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
|
1 2 3 4 |
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
|
1 2 3 4 5 6 7 8 |
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 & 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 :
|
1 |
Select EmpID, EmpName from Employee WHERE EmpID IN ( Select ProjectID from Project WHERE Status = ‘BENCH’) |
Anti join Example:
|
1 |
Select EmpID, EmpName from Employee WHERE EmpID NOT IN ( Select ProjectID from Project WHERE Status = ‘BENCH’) |
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 :
|
1 2 3 |
SELECT T_ID, T_Name From Teacher WHERE T_ID IN (Select T_ID From Department WHERE DEPTNAME = ‘COMPUTER SCIENCE’ ) |
Field Anti Join
|
1 2 3 |
SELECT T_ID, T_Name From Teacher WHERE T_ID NOT IN (Select T_ID From Department WHERE DEPTNAME = ‘COMPUTER SCIENCE’ ) |
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
|
1 2 |
SELECT T_ID FROM TEACHER WHERE WhoID IN (SELECT FROM DEPARTMENT WHERE DEPT_NAME =’Comp Science’) |
Reference Anti Join
|
1 |
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.
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
- Introduction to Cloud Computing (Salesforce.com and Force.com)
- Overview of Database Concepts (Salesforce.com)
- Introduction to Force.com
- Building Salesforce Custom App and Objects
- Object Relationships and Formula Field in Salesforce
- Salesforce Security Model and Overview
- Automation in Salesforce
- Approval Process in Salesforce
- Introduction to SOQL and SOSL
- Introduction to Apex
- Salesforce Data Management
- Visualforce MVC Architecture on Cloud
- Salesforce Reports and Dashboards
- Building a Visualforce (Custom) Page for the Salesforce App
- Salesforce Sandbox and Overview of Force.com capabilities
- Learning Apex and Deployment Tools
very nicely explained. SOSL seem to be very limiting in its use specially the number of records that you get returned.