SOQL - Salesforce Object Query Language

SOQL 

 Salesforce’s back-end database uses Oracle

• SOQL: Salesforce Object Query Language

• SOQL is used only to perform queries with the SELECT statement. SOQL has no equivalent INSERT, UPDATE, and DELETE statements

• Data manipulation is handled using a set of methods known as DML (Data Manipulation Language)

• One big difference you’ll notice right away is that SOQL has no such thing as SELECT *. Because SOQL returns Salesforce data, and that data lives in a multi-tenanted environment where everyone is kind of "sharing the database,” a wildcard character like * is asking for trouble

SOQL has two basic relationship query types that you need to remember:

• Child-to-parent
• Parent-to-children

Child-to-parent

• SELECT c.FirstName, c.LastName, a.Name FROM Account a RIGHT JOIN Contact c ON (c.AccountId = a.Id)

 Parent-to-Children

• SELECT Name, (Select FirstName, LastName FROM Contacts) FROM Account

SOQL Aggregate Functions

Function	Description
AVG()	Returns the average value of a numeric field.
COUNT() and COUNT(fieldName) and  COUNT_DISTINCT()	Returns the number of rows matching the query criteria.
MIN()	Returns the minimum value of a field.
MAX()	Returns the maximum value of a field.
SUM()	Returns the total sum of a numeric field.

Best Practices:

• use Filters in SOQL ( Where Clause)

• use indexed Fields in Where Clause

• Id
• Name
• OwnerId
• CreatedDate
• SystemModStamp
• RecordType
• Master-Detail
• Lookup Fields
• Unique Fields
• External ID Fields

• avoid these things

• Querying for null rows
• SELECT Id, Name FROM Account WHERE Custom_Field__c = null
• Negative filter operators
• SELECT CaseNumber FROM Case WHERE Status != ‘New’
• Leading wildcards
• SELECT Id, LastName, FirstName FROM Contact WHERE LastName LIKE ‘%smi%’
• Text fields with comparison operators
• SELECT AccountId, Amount FROM Opportunity WHERE Order_Number__c > 10