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:-
SELECT one or list of fields
FROM an Object
WHERE (optional block) filtering criteria
SELCET EmpID, Name
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 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.
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
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]}
System.debug(‘University Name :’+u.Name+ ‘College Name:’+c.Name);
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 :
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 & Anti-Join
When joins are applied on Fields then these are knows as field semi joins or anti joins
Field Semi Join :
SELECT T_ID, T_Name
WHERE T_ID IN (Select T_ID From Department WHERE DEPTNAME = ‘COMPUTER SCIENCE’ )
Field Anti Join
SELECT T_ID, T_Name
WHERE T_ID NOT IN (Select T_ID From Department WHERE DEPTNAME = ‘COMPUTER SCIENCE’ )
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
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 reference field.
Thats it in to introduction of SOQL and SOSL. If you need to refer previous articles, please go through below 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