SOQL in Salesforce

What is SOQL?

SOQL (Salesforce Object Query Language) is used to fetch the data from the Salesforce database. It is conceptually very similar to the SQL (Structured Query Language), but it is only specific to Salesforce databases.
The basic syntax followed by SOQL looks something like this :

SELECT Id,Name FROM Account

NOTE: Unlike SQL, the SOQL doesn’t support the wildcard (*) operator. This operator could be used in SQL to fetch all fields from a table but that is not possible is SOQL.

Running SOQL Queries

There are multiple ways to run SOQL queries in an salesforce org :

Developer Console

Open the developer console in your org, and go to the Query Editor tab on the bottom.

query-editor

Then we can write in the SOQL query statement inside of this section and click on Execute, which will run the query and return us the results.

soql-query

Workbench

Workbench is a tool available for salesforce which can be used to do quite a lot of stuff, one of it includes running SOQL queries too.
Go to the workbench URL and login from your salesforce org :

https://workbench.developerforce.com/l

Once logged in, you need to go to Queries -> SOQL Query

workbench

Then you can use the Query builder to build out a query or just directly put the query in a text box below. Then click on the Query button and you should be able to see the results..

query

result-query

Workbench provides additional features like being able to query on deleted records, running the query via the Bulk API and many more.

In Apex Code

Running SOQL queries in Apex code is essential to access your salesforce data inside an apex class. We can run a SOQL query like this in an apex class :

public class Account_Ctrl
{
	public list 	accs;

	public Account_Ctrl()
	{
		accs = [SELECT Id,Name FROM Account];

	}
}

In Apex, each SOQL query is enclosed within the square brackets, unless we use the database.query method which takes the query as a string.

list aacs = (list)database.query('SELECT Id,Name FROM Account');

List Example

The SOQL query returns a list of records which can be assigned to an list of Accounts in apex, like in the example like this :

list accs = [SELECT Id,Name FROM Account];

Map Example

Also, we can assign the returned records to a map in salesforce as well, like this,

map aacs = [SELECT Id,Name FROM Account];

This will automatically set the ID of the returned record into the map as a key, and the value will be the returned record for that id.

SOQL Where Clause

SOQL supports a way to apply filters to your queries by using a WHERE clause which behaves the same way like the SQL version. It allows you to create a logical statement with AND and OR operators to apply additional filters on the data returned in your SOQL query.

SELECT Id,Name FROM Account WHERE Name = 'Salesforce Drillers' AND Description != null

SOQL commonly used functions

Here are the few SOQL operators and functions that are used commonly:

LIMIT

SOQL Limit clause allows us to limit the maximum data which will be returned to us from an SOQL query.

SELECT Id,Name FROM Account limit 50

Group By and Order By

The group by clause can be used to group SOQL results based on certain fields, which can be used to apply aggregate functions in SOQL like COUNT, SUM, MIN and MAX.

SELECT COUNT(Id),Name FROM Account WHERE Group By Name

Note: The Group By keyword returns the AggregateResult list while you use that in apex. For example:

list aggr = [SELECT COUNT(Id),Name FROM Account WHERE Group By Name];

The order by clause is used to order the SOQL results based on a field in ASC (ascending, and default if not used explicitly) and DESC (descending) way.

SELECT Id,Name FROM Account Order By CreatedDate DESC

Relationship Fields in SOQL

Parent to Child

We can access relationship fields or parent or child records in SOQL too with the relationship name. For example if we want to query an Account record with all the Contact records under it, we can use the Contacts child relationship name to query it like this :

SELECT Id,Name,(SELECT Id,FirstName,LastName FROM Contacts) FROM Account

For any custom objects, the relationship name is appended with an __r suffix, signifying that it is a relationship field. This is what a parent to child query looks like on a custom object :

SELECT Id,Name,(SELECT Id,Name FROM Students__r) FROM Class__c

Child to Parent

For the other way around, if we want to fetch the parent account record’s information for a Contact record, we can use the Account lookup field to query it like this :

SELECT Id,FirstName,LastName,AccountId,Account.Name FROM Contact

And likewise if we do a query to fetch parent records on a custom object , we can again use the ___r appended relationship name of the field to query the data.

SELECT Id,Name,Class__r.Name FROM Student__c

Limits around SOQL

We can only fetch a maximum of 50,000 data rows in one transaction with SOQL queries. So make sure to use the LIMIT keyword to make sure that your SOQL doesn’t fetch more than 50,000 rows.
We can only do a total of 100 SOQL queries in one transaction.
We can only fetch one level down child objects in the query, for example we can only go from the Account to Contact.
While fetching Parent fields, the maximum level we can go upwards is 5 objects.

Salesforce Menu

Salesforce Lightning

Salesforce LWC

SOQL in Salesforce

Relationship Fields in SOQL