세일즈포스(Salesfroce) SOQL 예제 총 정리

SOQL Syntax

A typical SOQL query had the following structure:

SELECT [fields] FROM [object] [WHERE filter_expressions] [ORDER BY list_of_field_names ASC/DESC] [LIMIT n]

This query will be executed in the following ORDER:

1. Find records that satisfy the constraint filter_expressions
2. Sort the records by the specified data in either ascending or descending order and
3. Return the n first records

Keep in mind that WHERE, ORDER BY and LIMIT clauses are optional.

Following a SOQL query to get a maximum of 5 accounts in country France and order results based on Name field

SELECT Id, Name, BillingCountry FROM Account WHERE BillingCountry = 'France' ORDER BY Name ASC LIMIT 5

Steps to execute a SOQL query using The Developer Console :

1. Login to Salesforce using your credentials.
2. Open Developer Console from drop-down against your name.
3. Select the Query Editor tab from console.
4. Enter your query and click on execute to view results. for example:

SELECT Id, Name FROM Account ORDER BY Name  LIMIT 5

 

https://workbench.developerforce.com/query.php

 

Execute SOQL queries using Workbench

Workbench is a highly popular 3rd party tool that runs on top of Salesforce. It can be used to run anonymous apex, test Salesforce REST and SOAP API, or to execute SOQL queries against the Salesforce database on the fly.

Steps to execute a SOQL query using Workbench:

1. Go to https://workbench.developerforce.com/login.php
2. Login to your production/ sandbox org using your credentials.
3. When you login for the first time the app will prompt to give permissions to execute data in Salesforce, click allow.
4. After workbench opens, go to “queries → SOQL query” to open the query editor.
5. Enter your query and click on “query” to view the results.

 Fig : open SOQL query using workbench

 Fig: Execute a SOQL query in workbench

 

SOQL learning using Real Life Scenario

Over the course, we will be sharing various examples related to SOQL features. Let’s see the real-life use case, on which these will be explained.

Kyle Medical Group(KMG) is a healthcare provider across the USA, mainly related to heart diseases. KMG has several clinics in various states of the USA. KMG uses Salesforce to manage all the appointments for the patients.

KMG simplified data model:

Fig: Data Model of KMG Appointment Management System

1. The patients are represented in Contact standard object in Salesforce.
2. The clinics are represented by Clinic__c custom object in Salesforce.
3. An appointment is represented by Appointment__c custom object.
   • It has two lookup relationships, one with contact and another with clinic.
   • It’s a parent-child relationship.
   • A contact can have multiple appointments.
   • A clinic can have multiple appointments.
   • The appointment is a junction object.

IMPORTANT

Note that the custom objects and custom fields are denoted by appending "__c" after their name, whereas standard objects and standard fields (Account, Contact, Opportunity, ...) don't have these suffix.
These are referred to as API Names in Salesforce.

Now, when we move towards specific examples explaining the features of SOQL, we shall refer to this model, for example.

 

SOQL Naming Conventions

SELECT statements are used to fetch data from Salesforce by specifying which fields we want to retrieve. In addition to the fields we want to bring, we also have to tell Salesforce which Object to get. To do this we use the keyword FROM followed by the name of the Object.

IMPORTANT

SOQL queries uses API names when it refers to field names and object names. </br> In case of custom fields and custom objects, the API names end with "__c"

For example, if we want to see some records in the Contact Object, we could use the following query:

SELECT Id, FirstName, LastName, MRN__c FROM Contact

Note that only MRN (medical representative number) is written as MRN__c, meaning it is a custom field on a standard object. Id, FirstName, Lastname are standard fields and can not be appended with __c.

Now let’s take a look at SOQL on a custom object

SELECT Id, Name, Street__c, City__c , State__c FROM Clinic__c

 

Note that Id, Name is a standard field created automatically for all custom objects and cannot be appended by __c. Custom fields and Custom objects are appended with __c.

IMPORTANT

In SOQL, you cannot use the asterisk (*) to retrieve all object fields. You have to explicitly select the desired fields.

Combining the AND and OR Conditions

The logical_expressions which combine multiple logical operators must use parentheses to declare priority of one operator over the other. Otherwise, a syntax error is shown.

Example: Find the clinics which are either in zip code 32003 or 32004, and open on holidays.

Incorrect Query:

SELECT Id, Name FROM clinic__c WHERE mailingpostalcode = '32003' OR mailingPostalCode = '32004' AND is_open_on_holidays__c = True

Correct Query:

SELECT Id, Name FROM clinic__c WHERE (mailingpostalcode = '32003' OR mailingPostalCode = '32004') AND is_open_on_holidays__c = True

SOQL Order of execution

The SOQL order of execution defines the order in which the clauses of a query are evaluated. Some of the most common query challenges I run into could be easily avoided with a clearer understanding of the SOQL order of execution.

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. LIMIT

SOQL Where Clause

The SOQL WHERE clause is used to filter records that match a certain condition. For example, you could use the following query to return every account with the billing country equal to France:

SELECT Id, Name, BillingCountry FROM Account WHERE BillingCountry = 'France'

We can also combine multiple logical tests in our WHERE clause using AND or OR. If, for example, in our previous query, we only wanted to return the accounts with billing country equal to France that also had a billing city equal to ‘Paris’, we could use AND to specify both conditions.

SELECT Id, Name, BillingCountry, BillingCity FROM Account WHERE BillingCountry = 'France' AND BillingCity = 'Paris'

 

The following table show how to filter rows depending on field data type:

A string value is enclosed in single quotes	Country__c = 'USA'
A number value is written directly	Max_no_of_appointments__c  > 100
A boolean value is written as either true/false	Open_in_holiday__c = true
A date is written in this format (yyyy-mm-dd)	CreateDate > 2019-01-31
A datetime is written in this format (yyyy-mm-ddThh:mm:ssZ)	CreateDate < 2019-01-31T10:00:00-08:00

Note that a value can also be a date literal like (appointment_date__c = TODAY) which we will explain later.

 

 

SOQL Comparison Operators

The most straightforward approach to filter data is by using comparison operators.

The examples used in this section refers to the data model we introduced earlier.

Equal to =

This operator returns True when the value in the field matches the ‘value’ specified. For Strings, the match is case sensitive.

Example: Find all the clinics in Florida

SELECT Id, Name FROM Clinic__c WHERE State__c = 'Florida'

Not Equal to !=

This operator returns True when the value in the field does not match the ‘value’ specified.

Example: Find all the patients who do not have their email id registered

SELECT Id, FirstName FROM Contact WHERE Email != null

Greater than >

This operator returns True when the value in this field is greater than the ‘value’ specified in filter_expression.

Example: Find clinics which accept more than 100 appointments a day

SELECT Id,Name FROM Clinic__c WHERE Max_no_of_appointments__c > 100

Less than <

This operator returns True when the value in this field is less than the ‘value’ specified in filter_expression.

Example: Find all the patients with age more than 65 years.

SELECT Id, Name FROM Contact WHERE BirthDate < 1954-01-01

Greater than or equal to =<

This operator returns True when the value in this field is greater than or equal to the ‘value’ specified in filter_expression.

Example: Find clinics which accept 100 or more appointments a day

SELECT Id,Name FROM Clinic__c WHERE max_no_of_appointments__c >= 100

Greater than or equal to <=

This operator returns True when the value in this field is less than or equal to the ‘value’ specified in filter_expression. Example: Find clinics that accept up to 10 appointments a day.

SELECT Id,Name FROM Clinic__c WHERE max_no_of_appointments__c <= 10

SOQL LIKE operator

LIKE operator is similar to the SQL like operator. It is used to match a substring in the field’s values. It returns True if the string specified in the ‘value’ is present as a substring in the field’s value. The value specified can also contain special wildcard characters.

• The % wildcard denotes one or more characters.
• The _ wildcard denotes a single character.
• The % and _ can be matched with an escape character (\% , _).
• The LIKE operator can only work with string values.
• The search is case-insensitive, which is unlike String ‘equals to’ matching.
• The like operator is a complex operator and should be used wisely, as it can put a lot of load on the server.

Example #1: Find all patients whose MRN begins with 70. They denote patients in Florida.

SELECT Id, Name FROM Contact WHERE MRN__c LIKE '70%'

Example #2: Find all patients whose first name has a second letter equal to ‘u’.

SELECT Id, Name FROM Contact WHERE Firstname LIKE '_u%'

SOQL IN operator

The IN operator allows you to match a field’s value to multiple specific values in a single filter expression. The multiple values are written inside parentheses, each value enclosed in single quotes and separated by commas. It returns True if any one of the specified values matches the field’s value. IN operator works only on strings and is case sensitive.

Example: Find all the clinics in New York, Washington and Denver.

SELECT Id,Name,city__c FROM Clinic__c WHERE city__c IN ('New York', 'Denver', 'Washington')

SOQL NOT IN operator

The NOT IN operator, on the other hand, returns True if the value in the field does not match any of the specified values.

Example: Find all clinics which are not in the megacities New York, Washington and Denver.

SELECT Id,Name,city__c FROM Clinic__c WHERE city__c NOT IN ('New York', 'Denver', 'Washington')

TIPS

There is no limit on the number of values inside the IN clause. The only limit would be that you cannot exceed the max size of the SOQL query (20,000 characters)

SOQL IN operator with subqueries

In the previous section, we used the IN operator to match a field’s value with a list of values specified in brackets.

The IN operator can also be used with a subquery to match a field’s value with a list of values present in another query.

 

Example #1: Find all the clinics which had at least one appointment in the previous month.

SELECT Id, Name FROM Clinic__c WHERE Id IN (SELECT Clinic__c   FROM Appointment__c   WHERE Appointment_time__c = LAST_MONTH)

Here only those clinics will be returned whose ID we can also find on the Clinic__c field in a list of appointments made in the previous month.

Similarly, we can apply the NOT operator before IN:

Example #2: Find all the clinics which did not have any appointments in the previous month

SELECT Id,Name FROM Clinic__c WHERE Id NOT IN (SELECT Clinic__c   FROM Appointment__c   WHERE Appointment_time__c = LAST_MONTH)

IMPORTANT

While using IN clause with a subquery, only an ID field or a Relationship field, can be compared from the subqueries object. Otherwise, an SOQL error is thrown.

 

SOQL Logical Operators

When we need to filter rows using several conditions, we have to use Logical operators allow you to apply multiple comparison operators in one query.

Here is a list of logical operators available in SOQL:

The SQL AND operator

Allows you to select only rows that satisfy two conditions.

Example: Find all clinics which are open on holidays in Florida.

SELECT Id, Name FROM Clinic__c WHERE is_Open_on_holidays__c = True AND State__c = 'Florida'

The SQL OR operator

Allows you to select rows that satisfy either of two conditions.

Example: Find all clinics which are located in Florida or Chicago

SELECT Id, Name FROM Clinic__c WHERE State__c = 'Florida'  OR state__c = 'Chicago'

The SQL NOT operator

Allows you to select rows that do not match a certain condition.

Example: Find all clinics which are NOT located in Florida or Chicago.

SELECT Id, Name FROM Clinic__c WHERE NOT (state__c = 'Florida' OR state__c = 'Chicago' )

SOQL ORDER BY

The ORDER BY clause is used to sort the returned rows in the specified order. We can sort ascending or descending by specifying ASC or DESC keyword.

If we do not specify anything, ascending order is used. We con sort multiple fields at the same time, and the sorting order is left to right.

We can sort text, number, date, datetime and text fields.

Example #1: Find all the clinics in descending order of their name.

SELECT Id, Name FROM Clinic__c ORDER BY Name DESC

Example #2: Find all the clinics in various states and cities and sort them first by states, and then by cities.

SELECT Id, Name FROM Clinic__c ORDER BY state__c, city__c

You can also sort based on an aggregated field in GROUP BY clause.

Example #3: Find the clinics and their total booking amount in the previous month. Sort the results in descending order of the total booking amount.

SELECT Sum(Booking_Amount__c), Clinic__c FROM Appointment__c GROUP BY Clinic__c ORDER BY Sum(Booking_Amount__c) DESC

TIPS

You can sort by ascending and descending in different fields in the same query.

 

SOQL LIMIT

The LIMIT clause is used to limit the number of rows returned by a SOQL query. The maximum number of records that should be fetched is specified in the Limit clause. The LIMIT clause is optional, but it is highly recommended to use

Example #1: Find the first 50 clinics in Florida ordered by their name.

SELECT Id, Name FROM Clinic__c ORDER BY Name LIMIT 50

You can also apply a limit on SOQL queries containing group by clause. In that case only the first few rows of aggregated records are returned.

Example #2: Find the top 10 clinics with maximum revenue from booking amount.

SELECT Sum(Booking_Amount__c), clinic__c FROM Appointment__c GROUP BY Clinic__c ORDER BY Sum(Booking_Amount__c) DESC LIMIT 10

 

SOQL OFFSET

The OFFSET clause is used to specify the cursor or the number of records that should be skipped before returning results from the query. This clause is primarily used when we want to break the set of records into various pages, or batches.

Example: Let’s say we want to show the list of clinics on a page. And we want to apply pagination, so each page contains a maximum of 25 clinics.

The SOQL query for Page 1:

SELECT Id, Name FROM Clinic__c LIMIT 25

The SOQL query for Page 2:

SELECT Id, Name FROM Clinic__c OFFSET 25 LIMIT 25

The SOQL query for Page 3:

SELECT Id, Name FROM Clinic__c OFFSET 50 LIMIT 25

The SOQL query for Page n:

SELECT Id, Name FROM Clinic__c OFFSET 25*(n-1)  LIMIT 25

We can easily see how it is straightforward to break records using the OFFSET clause.

 

SOQL Aggregate Functions

The SOQL aggregate functions are used to calculate and return a single value by aggregating the data of a field FROM the returned SOQL records. Aggregate functions are similar to pivot table in Excel.

Aggregate functions can be used to count records, sum numeric field in the returned records, or to return the minimum and the maximum number or date from the returned data.

The SOQL aggregate functions can also be used to generate simplified reports using the GROUP BY clause, by aggregating data in various categories.

For example, you can get the number of clinics for each state, or region.

SELECT Count(Id), State__c FROM Clinic__c GROUP BY State__c

COUNT(ID)STATE

120	Florida
10	Illinois
28	Nevada

 

We get here, the number of clinics for each state. The rows above are also called as aggregated rows.

Let’s take a look at all the aggregate functions supported in SOQL:

SOQL COUNT

COUNT() is used to find the total count of returned rows in SOQL. COUNT(field_name) is used to find the total count of rows which contain non null field values.

Example #1: Find the count of appointments in the previous month.

SELECT Count() FROM Appointment__c WHERE Appointment_time__c = LAST_MONTH

Example #2: Find the count of patients who have an email address present in Salesforce.

SELECT count(Email) FROM Contact

Contact

Example #3: Find the count of clinics by various states.

SELECT Count(Id),State__c FROM Clinic__c GROUP BY state__c

state__c

SOQL AVG

To find the average of values in a field returned by SOQL. This operator can only be used with Number fields.

Example: Find the average booking amount earned by appointments in various clinics in the previous month.

SELECT AVG(booking_amount__c), Clinic__c FROM Appointment__c WHERE Appointment_time__c = LAST_MONTH GROUP BY Clinic__c

SOQL SUM

To find the sum of values in a field returned by SOQL. This operator can only be used with Number fields.

Example: Find the total booking amount earned by appointments in various clinics in the previous month.

SELECT SUM(booking_amount__c), Clinic__c FROM Appointment__c WHERE Appointment_time__c = LAST_MONTH GROUP BY Clinic__c

ic__c

SOQL MIN

To find the minimal field value in the records returned by SOQL. This operator can only be used with Number fields.

Example: Find the oldest patient’s birthdate for KMG.

SELECT MIN(Birthdate) FROM Contact

ntact

SOQL MAX

To find the mimaximal field value in the records returned by SOQL. This operator can only be used with Number fields.

Example: Find the maximum booking amount charged on a single appointment by each clinic in previous month.

SELECT MAX(booking_amount__c), Clinic__c FROM Appointment__c GROUP BY Clinic__c

linic__c

IMPORTANT

If a SOQL query contains the >GROUP BY clause, all the fields which are fetched, must either be grouped or aggregated.

 

 

SOQL GROUP BY

SOQL does support grouping by multiple fields. The grouping order is from left to right.

Example! Find the count of clinics in various states and cities.

SELECT Count(Id), State__c, City__c FROM Clinic__c GROUP BY State__c, City__c

This query will return a table of this type:

 

COUNT(ID)	STATE__C	CITY__C
40	Florida	Orlando
80	Florida	Miami
3	Illinois	Chicago
7	Illinois	Rockford
12	Nevada	Las Vegas
10	Nevada	Reno
6	Nevada	Henderson

Using Aliases in GROUP BY

In SQL, we can alias any column being fetched from the database table, but in SOQL, this is not allowed.

However, we can still alias fields fetched when the SOQL query contains a group by clause.

SELECT count(Id) No_Of_Clinics, State__c state FROM Clinic__c GROUP BY State__c

Note that alias notation does not use as like SQL does.

It is also important to note, that even if you don’t specify an alias, all aggregated fields automatically get an implied alias in the form expr(n) where n starts from 0, and from left to right.

For Instance, Query:

SELECT Count(Id), SUM(Booking_Amount__c), Clinic__c FROM Appointment__c GROUP BY Clinic__c

Here Count(Id) automatically becomes expr0 and SUM(Booking_Amount__c) becomes expr1

Then to access these fields, you have to use their respective aliases in Apex.

 

The HAVING clause in SOQL is almost similar to WHERE except that HAVING allows you to filter using functions such as SUM, COUNT, AVG, MIN or MAX.

Example:

Find all clinics where the total booking amount in the previous month exceeds 10000 USD.

SELECT Sum(Booking_Amount__c), clinic__c FROM Appointment__c WHERE Appointment_time__c = LAST_MONTH HAVING Sum(Booking_Amount__c) > 10000

Note how the WHERE clause and HAVING clause are used together in the above query.

The WHERE clause applies a filter on the rows to retrieve, and then these rows will be aggregated. The HAVING clause applies a filter on the aggregated rows returned finally.

 

SOQL Relationship Queries

A Salesforce relationship is established between 2 objects by creating a lookup or master-detail relationship.

For instance, Appointment__c has two lookups fields Patient__c (lookup on Contact object) and Clinic__c (lookup on Clinic__c object). These are custom lookup fields as we created them only in our Salesforce instance. The object on which the field is defined, here appointment__c, becomes the child object, and contact/clinic__c becomes the parent.

Similarly, we have a field AccountId on Contact establishing a lookup relation from Contact to Account. So in this relationship, Account is the parent, and Contact is the child.

Fig: Relationship between Account and Contact Object.

Now SOQL allows us to access data from child to parent, as well as parent to child. Let’s see how:

SOQL Child-to-Parent Query

To access the parent’s field from the child, SOQL uses dot (.) notation.

Example #1: Fetch all contacts name, as well as account’s name and ID.

SELECT Id, Name, Account.Name, Account.Id FROM Contact

Example #2: Fetch all the appointments of the patients who have made an appointment in Orlando clinic.

SELECT Id, Patient__r.Name FROM Appointment__c WHERE clinic__r.Name = 'Orlando'

Example #3: Find patients name, patients account name who have made an appointment in Orlando clinic.

SELECT Id, Patient__r.Name, Patient__r.Account.Name FROM Appointment__c WHERE Clinic__r.Name = 'Orlando'

Notice that we have traversed 2 parent relationships to get the Account Name: Patient__r.Account.Name

Example #3: Find the appointments created by Laura Magson.

Select Id, Name FROM Appointment__c WHERE CreatedBy.Name = 'Laura Magson'

What you have to remember:

The custom lookup relationships are fetched by applying ‘__r’ at the end, and then followed by . and field name. The pattern is : Parent_Object__r.Cutom_Field__c.
CreatedBy is a default field created automatically and is a lookup to the User Object.
The maximum number of relationships we can traverse like this is 5.
If a parent is not found, SOQL returns NULL for that field.
SOQL Parent-to-Children Query
We can fetch child records by using nested queries

Example #1: Find all the accounts, and all the contacts under that account.

Select Id,Name, (Select Id,Name from contacts) FROM Account

Example #2: Find the clinics in Florida, and also fetch the appointments made for each clinic in Florida.

SELECT Id, Name, (SELECT Id,patient__r.name,clinic__r.name FROM Appointments__r) FROM Clinic__c WHERE State__c = 'Florida'

This query returns all the clinics in Florida, and for each clinic, the appointments made for that clinic.

Here is how the query runs in the Workbench.

 Fig: Showing how a parent to child query returns results in workbench

What you have to remember:

• SOQL subquery is enclosed in brackets, which is mandatory.
• The subquery supports WHERE, ORDER BY and LIMIT clauses like a normal SOQL.
• For Standard Object (like Contact) the parent-to-child relationship is a plural name. For example, the relationship name for the Contact Object is Contacts.
• For Custom Object (like Appointment__c) the parent-to-child relationship is a plural name appended with __r. For example, the relationship name for the **Appointment__c Object is Appointments__r.
• You can find this child relationship name by opening the field from the setup menu in that object.

SQL JOIN VS SOQL Relationships

SQL allows you to combine data from two or more tables. These tables can be any tables, and JOIN can be performed on any column. In Salesforce SOQL, if we want to fetch data from more than one object, there must be a relationship (lookup or master-detail) between the two objects. Salesforce does not have an explicit JOIN keyword.

Let’s assume an SQL database that has two tables:

• Account table with two columns Id and Name
• Contact table with 4 columns Id, FirstName, LastName and AccountId

Let’s say we want to fetch all contacts and also the account’s name, which is a child to parent query we discussed earlier.

 

In SQL, we will have to perform a Right Outer Join:

SELECT c.Id, c.FirstName, c.LastName, a.Name from Account a Right Join Contact c ON (c.AccountId = a.Id)

The equivalent SOQL query would be:

Select Id, FirstName, Lastname, Account.name from Contact

Thus child to parent query corresponds to a right outer join in this case.

As you could see, the SOQL is more intuitive to read. Note that even if field AccountId is null, the records will be fetched in SOQL.

Now, let’s see how we can implement a Left Outer Join in SQL:

Select c.Id,c.FirstName,c.LastName,a.Name from Account a LEFT Join Contact c ON (a.Id = c.AccountId)

This query will fetch all accounts, and also all contacts related to that account.

The equivalent SOQL query would be:

Select Id,Name, (Select Id,FirstName,LastName from contact) FROM Account

The Parent to child nested query corresponds to a left outer join in this case.

To implement an Inner Join, we can use the IN operator: SOQL query:

Select Id, Name from account where Id IN (Select AccountId from contact)

This query will return only those accounts which have at least one contact. We discuss in-depth about the IN operator in subqueries in the next section.

 

SOQL Date Literals

SOQL has a brilliant and time-saving feature for comparing date values, which is called Date Literals. They are fixed string constants used to specify a date range without the need to quote specific dates. They can work with both with Date and Datetime Comparisons.

Say, for instance, you want to find all the patients whose birthday is in the current month. Let’s assume the current Month is August 2019.

So SOQL query can be:

SELECT Id, Name FROM Contact WHERE Birthdate >= 2019-08-01 AND Birthdate <= 2019-08-31

This query works, but there is a lot of effort.

You have to determine the current month.
You have to calculate the start date and end date of the month.
You have to check if the month contains 30 or 31 days. And yes, 28, 29 sometimes.
The requirement can be handled very easily by using Date Literals.

The same SOQL query can be rewritten as:

SELECT Id, Birthdate FROM Contact WHERE Birthdate = THIS_MONTH

A lot more intuitive query, easier to read, and easier to maintain.

IMPORTANT
SOQL Date Literals are case insensitive.

 

Following a list of the most used Date literals, you can find an exhaustive list here.

DATE LITERALDESCRIPTIONEXAMPLE

YESTERDAY	Begins at 00:00 in the previous day, and continues for 24 hours	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = YESTERDAY
TODAY	Begins at 00:00 in the current day, and continues for 24 hours	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = TODAY
TOMORROW	Begins at 00:00 the next day, and continues for 24 hours	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = TOMORROW
LAST_WEEK	Begins at 00:00 on Monday for the previous week, and continues for 7 full days	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = LAST_WEEK
THIS_WEEK	Begins at 00:00 on Monday of this week, and continues for 7 full days	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = THIS_WEEK
NEXT_WEEK	Begins at 00:00 on Monday for next week, and continues for 7 full days	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = NEXT_WEEK
LAST_MONTH	Begins at 00:00 on 1st day of the previous month and continues till the last day of that month	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = LAST_MONTH
THIS_MONTH	Begins at 00:00 on 1st day of this month and continues till the last day of this month.	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = THIS_MONTH
NEXT_MONTH	Begins at 00:00 on 1st day of the next month and continues till the last day of next month.	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = NEXT_MONTH
THIS_YEAR	Begins at 00:00 on 1st day of this Year, and continues till the last day of the year.	SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = THIS_YEAR
LAST_N_DAYS:n	Begins at 00:00 in the current day and continues for n past days. n is a positive Integer variable.	Appointments for the past 3 days SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = LAST_N_DAYS:3
NEXT_N_DAYS:n	Begins at 00:00 the next day, and continues for n future days.n is a positive Integer variable.	Appointments for the next 3 days SELECT Id FROM Appointment__c WHERE Appoitntment_time__c = NEXT_N_DAYS:3

 

SOQL Date Functions

SOQL supports some date functions which can be applied on Date and Datetime fields value in the WHERE Clause before comparing.

Here are a few commonly used Date Functions:

FUNCTION NAMEDESCRIPTIONEXAMPLE

CALENDAR_MONTH	To find the Integer value of the current month. 1 for January, 12 for December	SELECT Id, Name FROM Contact WHERE Calendar_MONTH(Birthdate) = 8 Patients born in August.
CALENDAR_YEAR	To find the year of a date field.	SELECT Id FROM Contact WHERE Calendar_YEAR(Birthdate) > 1965 Patients born after 1965
DAY_IN_WEEK	To find the day at which the date points. 1 for Monday, 7 for Sunday	SELECT Id FROM Contact WHERE DAY_IN_WEEK(Birthdate) = 7 Patients born on a Sunday

These functions can not be used in Select Clauses while returning field values, unless used in a special Group BY Clauses which we see later.

For instance, the following query throws a syntax error:

SELECT Id, CALENDAR_YEAR(BirthDate) FROM Contact

 

SOQL Query on History Objects

We can turn on history tracking on some standard and any custom object for specific fields. In case that field is changed, a history record is created, specifying the old value, and the new value, and who updated it.

SOQL can query these records as well:

Query on standard object’s history Object:

SELECT Field, OldValue , NewValue, CreatedBy.Name
FROM ContactHistory
WHERE ContactId = '003aertyulopuioQWE'
In the above query, all contact history records are fetched for contact whose id is ‘003aertyulopuioQWE’

Query on custom object’s history Object:

SELECT Field, OldValue , NewValue, Parent.Name
FROM Clinic__History
WHERE Parent.Name = 'Orlando'
IMPORTANT
Note that for Contact the history object is ContactHistory, whereas for Clinc__c, it is Clinic__History.
History Object fields:
- ParentId is the Id of the record.
- Field is the name of the field.
- OldValue is the old value of the field.
- NewValue is the new value of the field.

 

SOQL Query on Sharing Objects

Salesforce has a highly advanced security model, and we can specify at the record level, who has access to any record. The access for each record is stored in that record’s sharing object. And SOQL query can help us to fetch this information.

Query on a standard object’s share records:

SELECT Id, AccountId, UserorGroupId, AccountAccessLevel, RowCause FROM AccountShare WHERE AccountId = '001frgthyjugggt001'

Query on a custom object’s share records:

SELECT Id, AccountId, UserorGroupId, AccountAccessLevel, RowCause FROM Clinic__Share WHERE ParentId = 'a0K0o00000o7gm5'

IMPORTANT
Note that for Account the Share Object is AccountShare, whereas for Clinic__c, it is Clinic__Share.

 

Sahre Object fields:
ParentId: The ID of the record being shared.
UserorGroupId: The Id of the user, or the public group with which the record is being shared.
AccessLevel: Read/Edit.
RowCause: The reason why the record is shared with that userOrGroupId.

 

SOQL in APEX code

We learnt a while ago, how we can execute SOQL queries to return data on the fly in Developer Console, and Workbench. And now that we have learnt most of the SOQL features, we finally see the use of SOQL queries inside Apex programming language. Apex is Salesforce’s own programming language similar to JAVA in operation which runs and operates on Salesforce servers.

We can run apex code directly in Workbench or Developer Console to test apex code. In actual scenarios, the code will be written inside apex classes inside methods or functions and called via Visualforce Pages, Lightning Components, Object Triggers, etc.

Here is a sample snippet of Apex code using a simple SOQL query to fetch 10 patients:

List contactList = [SELECT Id, Name   FROM Contact   ORDER BY Name DESC   LIMIT 10]; for(Contact con :contactList){   System.debug('Name:' + con.Name); }

When you run this as an anonymous block inside workbench, you will see the first 10 contacts in descending order with their name being printed.

Key Things to know:

SOQL query is written in square brackets inside apex.
SOQL query returns a list of records which can be stored inside List data structure of Apex.
Only the fields mentioned in the query are retrieved and if we try to access a field other than the specified fields, we may get a runtime error. For eg, system.debug(con.email) will throw an error.

 

Steps to execute this Apex Code in workbench:

1. Go to https://workbench.developerforce.com/login.php
2. Login to your production/sandbox org using your credentials.
3. When you login for the first time the app will prompt to give permissions to execute data in Salesforce, click allow.
4. After workbench opens, go to “utilities →APEX execute” to open the anonymous code editor.
5. Enter your apex code and click on Execute.

 Fig How to open anonymous apex window in workbench

 Fig: How to execute an anonymous apex code snippet

 

Apex Variables in SOQL Queries

Bind expressions are used in the WHERE clause to compare the field’s value with an Apex variable. It uses the following syntax:

WHERE field_name comparisionOperator :bind_variable

Consider this Apex snippet:

String clinicName = 'Orlando'; List<Appointment__c> appointmentList = [SELECT Id, Patient__r.Name FROM Appointment__c WHERE Clinic__r.name = :clinicName];

In this example, the name of the clinic is compared with a variable clinicName. You will find it extremely helpful if the code was in an apex method where values were passed as variables.

Example: Design an apex method that returns the maximum number of appointments it can handle in a day given the clinic’s name.

public Integer geMaxNoOfAppointments(String clinicName){   List<Clinic__c> clinicList = [SELECT Id, Name, max_no_of_appointments__c     FROM Clinic__c   WHERE name= :clinicName];   return clinicList [0].max_no_of_appointments__c ; } System.debug(geMaxNoOfAppointments('Orlando'));

 

Static and Dynamic SOQL

We have two ways to invoke SOQL queries in Apex code: Static and Dynamic SOQL.

Static SOQL
Well, if you have read the examples in the previous sections, you already know about Static SOQL. The Static SOQL query is written inside square brackets and returns a list of records meeting the criteria. Thus while writing the query, you know which object to query, which fields to query, and what conditions to apply. The only thing that can vary is the use of Bind Expressions.

Example: Fetch Name, max_no_of_appointments__c of all the clinics that are in Florida.

List<Clinic__c> clinics = [SELECT Id,Name,max_no_of_appointments__c     from clinic__c   where state__c = 'Florida'];

Here we knew we had to query on clinic__c, and which fields to fetch, and which fields to apply filter conditions on.

Dynamic SOQL
In Dynamic SOQL, we generate the SOQL query at run-time as a string. The name of the object, the names of the field need not be known in advance. Therefore these can be used to design more flexible applications when the fields to apply filters on are not known in advance.

Dynamic SOQL can be invoked by Database.query(query_string); where query_string is the query generated at runtime. In operation and processing, it works the same as Static SOQLs.

Example :

List<Contact> conList = Database.query('SELECT Id, Name   FROM contact   WHERE firstname= \'James\' ');

Did you see that to compare strings or any quoted value, we need to escape the quotes with the backslash, as the query is also written within quotes. Eg 'James'.

Another example where fields are not known in advance:

String fieldnames = 'Id, Name, Email'; String query = 'SELECT '+ fieldNames + ' FROM Contact'; List<Contact> conList = database.query(query);

Here field names are supplied externally. This could have easily been an apex function which accepts fieldnames as a parameter.

Dynamic queries also support bind expressions in the same syntax as static SOQL. However, only simple bind expressions are supported. Bind expressions from an object’s field are not supported.

Example of a query that works perfectly fine:

String str = 'James'; database.query('SELECT Id FROM Contact WHERE firstname = :str ');

Example of a query that throw a a runtime error.

Contact con;

database.query('SELECT Id FROM Contact WHERE firstname = :con.firstname');

Key Things to Note
- Any compile-time errors are not thrown while saving a dynamic query.
- You have to be extremely cautious to prevent runtime errors.
- Dynamic SOQL can also cause potential security threats through SOQL injection. A malicious user might intentionally pass some characters which can retrieve more SOQL results that intended. If you must use dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input. This method adds the escape character () to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.

 

SOQL return types

When used in Apex code, a SOQL query can return three type of result: a list of sObjects, a single sObject, or of Aggregate Results.

SOQL query that return a List<sObject>
We have already seen this:

List conList = [SELECT Id, Name FROM Contact];

SOQL query that return a Single sObject
If only 1 row is returned, it can be assigned to a single Object as well.

Contact conObj = [SELECT Id,Name FROM Contact limit 1];

In this case, if the query does not return any rows, a runtime error will be thrown as nothing can be assigned to conObj.

SOQL query that return a List<AggregateResult>
For all group by clauses, an aggregate result is returned in APEX: This can be iterated to find individual values using the get function.

List apts = [SELECT Count(Id) nbrOfAppointments, clinic__c   FROM Appointment__c]; for(AggregateResult ar: apts){   system.debug('clinic:'+ ar.get(clinic__c));   system.debug('count:'+ ar.get(nbrOfAppointments)); }

Note that field aliasing is used for count(Id) nbrOfAppointments. Aliasing is only supported for aggregate functions inside Group By clause.

 

SOQL Governor Limits

Since Salesforce has a multi-tenant cloud architecture, the Salesforce platform has to put some limitations to prevent a single tenant from monopolizing the database servers CPU and affecting the performance for all the other tenant. That is how Salesforce is able to support millions of users.

Maximum number of records retrieved in a single transaction
The maximum no of records that can be retrieved in a SOQL query are 50,000. If more than 50,000 records are returned, an error is thrown.

To avoid this error, we should apply appropriate filters with WHERE clause to limit the number of records returned with the LIMIT clause.

Maximum length of SOQL statements
The maximum length of a SOQL statement is 20,000 characters. If there are more characters, an error: “Query too Complicated” is returned.

Maximum number of SOQL queries in a single transaction
If the number of SOQL queries running in a single transaction exceeds 100, the very common “Too many SOQL queries: 101” is thrown. To avoid these this error, we should avoid performing SOQL queries inside for loop.

For example:

List conList = new List(); List emailsToFind; for(String str :namesToFind){   Contact con = [SELECT Id, Name FROM contact WHERE name= :str];   conList.add(con); }

Here if we are finding more than 100 names, “Too many SOQL queries: 101” error will be returned, as for every name to find, we are using a separate query.

To solve we can use the IN operator in this case:

List conList = [Select Id,Name   FROM Contact   Where Name IN :namesToFind];

As a rule, never perform SOQL queries inside for loops.

 

Efficient SOQL Queries

Here are some recommendations to follow to improve SOQL queries performance:

Apply logic inside queries
Instead of fetching all records and then applying your filter logic, you should only fetch records, and fields which are necessary.

For instance:

List conlist = [SELECT Id,BillingState FROM Contact]; List conListFlorida = new List(); for(Contact conObj: conlIst){ if(conObj.BillingState == 'Florida'){ conListFlorida.add(conObj);   } }

Here, we could have easily created conListFlorida by:

List conlistFlorida = [SELECT Id,BillingState    FROM Contact   WHERE BillingState = 'Florida'];

Consider using SOSL for faster search
We discussed wildcards in the LIKE operator. When the search is on a large number of records and contains wildcards this might degrade performance. Salesforce has a native SOSL (Salesforce Object Search Language) which can help to improve search speed.

Avoid using comparison operators with Text fields
It is difficult to compare 2 Strings and might take a long time if your object has a large number of rows. So, as much as possible we should not compare a field’s value with comparison operators (<, >, <=, >=).

Explicitly filter out Null values
Example:

SELECT Id, account.BillingState  FROM Contact WHERE AccountId != Null AND Account.BillingState = 'Florida'

Here only those contacts will be fetched which have an accountID, and then the filter will be applied on account’s billing state.

Make SOQL queries Selective by applying indexed search
SOQL query is termed selective if we apply a where clause to an index field. If you have studied databases in-depth, you might know those database columns that are indexed can be searched faster and more efficiently.

By default, Salesforce index the following fields:

Standard fields: Id, Name, CreatedDate, LastModifiedDate
Relationships fields: lookup and master-detail relationships
Text fields marked as unique
External ID fields
These are standard indexes. You can also raise a case to Salesforce to make a custom field as indexed. This might be helpful when you are querying on a field too often, and it is causing performance issues.

Thus applying a WHERE filter on index fields can make a query selective. There is an exception though. If there are more than 1 million records of the object being queried, the index filter should return 30% threshold of first million, and 15% threshold for the remaining records, otherwise the index will not be applied and the query will be non-selective. In other words, if the SOQL query engine accepts the index to run the query, we call it a selective query.

Note that this threshold is 10% and 5% for custom indexed fields, instead of 30%, and 15%.

The following query is a selective query as the filter is applied on createdDate which is a standard index field.

Select Id, Name  FROM Appointment__c  WHERE CreatedDate = this_year

In queries containing more than 1 filter_expression, the query engine determines if, and which index should be applied.

Apply Sort Optimization
Let’s say you applied an index on a custom field to improve performance by raising a case with Salesforce. It worked perfectly, but as data grew, and grew, the index is no longer applied because it does not meet the threshold requirement (10%). And your query is now running slow.

Interestingly, you can apply ORDER BY clause with a LIMIT clause to mitigate this. You might wonder, that a ORDER BY operation might increase the load of the query, instead of helping it. The reality is indexed fields are always sorted, and thus no extra load is shed on the query. Together with a limited number of rows to fetch, thanks to LIMIT clause, the SOQL engine might select the index even if it does not meet the threshold requirement. However make sure that the field you are sorting on does not contain NULL values, otherwise, the index will not be applied.

Sort optimization is useful, when you are querying most recent updated records, or want to fetch records in a boundary (Top 50, Last 50, etc).

Avoid filtering on NULL values
By default, the NULL values in a custom field index are not maintained in index tables, and if we want to filter a field which is equal to null, the index will not be applied. Consequently, the query will scan all millions of rows to find the required data, and performance would degrade.

Example:

Select Id  FROM Contact  WHERE MRN__c = null

Here even if a custom index is applied on MRN__c, it will not be selected, and the query will not be selective.

Consider using skinny tables
Salesforce can allow you to create skinny tables, which are a copy of the object’s table, can contain up to 100 fields, can include both standard and custom fields.

How does a Skinny table increase performance:

There is no need to join standard and custom field tables. Conceptually, Salesforce maintains a separate table for standard and custom field for objects and performs an internal join while doing the queries. For a skinny table, this join is eliminated.
Skinny tables do not contain soft deleted records(records in the recycle bin) and therefore run faster.
These Principles work for Report and List Views as well
Many of the features we discussed, work equally well, and should be applied while creating Salesforce Reports and List Views. As you can imagine, both Salesforce Reports and List Views display a set of fields, and we can apply filters and sorting on the fields of the report. The underlying architecture for them is, you already know, SOQL.

Always plan for the future ahead
While designing your queries you must always see how that data is going to grow over time, and how it might degrade performance. A non-efficient query might be working just fine, for now, but can fail when data size grows. To prevent a break/ performance degrade always apply the best practices mentioned above.