Salesforce SOQL | Basics Simplified - Part 1

Salesforce Object Query Language

Until now in the previous episode, we have discussed salesforce apex collection like list, set, map.

In this episode, we are gonna discuss SOQL i.e. Salesforce Object Query Language.
Let's get started, SOQL(Salesforce Object Query Language) is used to query the records from the database based on the requirement.

The major difference between SQL and SOQL is, SQL is used to get the data from one or more tables even if they are not related to each other whereas in SOQL is used to get data of a particular object or related object.

There are 2 types of SOQL statements
1. Static SOQL
2. Dynamic SOQL

1. Static SOQL :

Static SOQL statement is written in [ ] array brackets.

These statements are similar to LINQ

For Example :

=============================================

string searchfor = 'SalesforceKid' ;

Contact[] Contacts = [SELECT xyz__c, Firstname, Lastname FROM Contact WHERE Lastname = : searchfor];
=============================================

2. Dynamic SOQL :

It is used to refer to the collection of a SOQL string at runtime with the apex code.
Dynamic SOQL enables you to create a more flexible application.

To create a Dynamic SOQL query at runtime use Database.Query() method, in one of the following ways.

It returns a single sObject when the query returns a single record.
For Example : sObject s = Database.query(string_limit_1)

It returns a list of sObject when query returns more than a single record.

For Example :

=============================================

string myTestString = 'TestName' ;

List<sObject> sl = Database.query(SELECT Id,Name FROM myCustomObject__c WHERE Name=: myTestString);

=============================================

You will have more clear vision once we started using SOQL in more examples. Now let's understand the syntax of SOQL query

Syntax :

=============================================

[SELECT field1,field2,.......FROM ObjectType WHERE condition]

Examples :

List<Account> acc = [SELECT Id, Name FROM Account];

List<Account> acc = [SELECT Id, Name FROM Account WHERE annualrevanue < 10000];

=============================================

Now let's talk about a scenario or requirement basis SOQL queries (When To Do What).

#1. Write a query to fetch customer Name, Balance from customer object where balance is more than 10000.
==============================================
List<Customer__c> Customer = [SELECT Id, customer_name__c,
FROM customer__c where balace__c > 10000];
==============================================

#2. Write a query to fetch TID, Type__c from a transaction if the mode is 'cash'.
==============================================
List<Transaction__c> tns = [SELECT Name, Type__c FROM Transaction__c WHERE Mode__c = 'cash'];
==============================================

Now when you want to write a query with a condition based on multi select picklist in which multiple item values are selected then we can use the following operators to write the condition.

Let's create IdProof Field on Customer__c with datatype multi-select with the following values :
1) Passport
2) Aadhar Card
3) Voter Id
4) PAN Card

Now let's talk about the requirement based on which we can write our query :

#1. Write a query to fetch all the customer names & account types who have submitted PAN Card as proof.
==============================================
List<Customer__c> customer = [SELECT Id, Customer_name__c, Account_Type__c FROM Customer__c WHERE IdProof__c = 'PAN Card'];
==============================================

#2. Write a query to fetch customer name, the balance from the customer object where proof is PAN Card or Aadhar Card.

NOTE: When you want to verify whether the value what we have selected is in the list or not by using INCLUDES.
==============================================
List<Customer__c> Customer = [SELECT Customer_Name__c, Balance__c FROM Customer__c WHERE Proof__c INCLUDES ('Aadhar Card'), ('PAN Card')];
==============================================

Now let's understand some operators in SOQL as well as How and when to use it.

LIKE :

An expression is true if the value spin the specified field name matches the characters of the text string in the specified value.
The LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL.
It provides a mechanism for matching the partial text string and includes support for wild cards.
The % and _ wildcards are supported for the LIKE operator.
The % wildcard matches zero or more characters.
The _ wildcard matches exactly one character.
The text string in the specified value must be enclosed in single quotes.
The LIKE operators are supported for string fields only.
The LIKE operator performs a case insensitive match, unlike case sensitive match in SOQL.

For Example :

=============================================

SELECT AccountId, Firstname, Lastname FROM Contact WHERE Lastname LIKE 'test_%'

=============================================

IN :

If the value equals any one of the specified values in a WHERE clause.

For Example :

=============================================

SELECT Name FROM Account WHERE BillingState IN ('Californa', 'Newyork')

=============================================

NOT IN :

If the value does not equal any of the specified values in a WHERE clause.

For Example :

=============================================

SELECT Name FROM Account WHERE BillingState NOT IN ('California','NewYork')

=============================================

Now let's discuss how to use a subquery.

SUBQUERY :

The query within a query is called subquery.

Most commonly we use a subquery when there is a relationship between two objects.

#1 Example :

=============================================

SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)

=============================================

In the above query AccountId is a lookup field in the opportunity.

#2 Example :

=============================================

SELECT Id FROM Opportunity WHERE AccountId NOT IN (SELECT AccountId FROM Contact WHERE LeadSource = 'web')

=============================================

NOTE: Whenever we create a master-detail or lookup field id of the master record is stored in this field.

Date Format :

When you want to use the date formats in a query, it should be any one of the below formats.

Format Format Syntax Example

Date Only YYYY-MM-DD 1991-01-01

Date, time YYYY-MM-DDThh: 1991-01-01T23:01:01+01:00
& time zone mm:ss+hh:mm
offset

LIMIT :

Use LIMIT to specify the maximum number of rows to return.

Syntax :

=============================================

SELECT fieldList FROM ObjectType [WHERE ConditionExpression] LIMIT number_of_rows

=============================================

For Example :

=============================================

SELECT Name FROM Account WHERE Industry = 'Media' LIMIT 125

=============================================

The above query LIMIT the records by 125 that means it will retrive only first 125 records.

NOTE: you can't use a LIMIT clause in a query that uses an aggregate function, but does not use a GROUP BY clause.

For Example :

=============================================

SELECT MAX(createdDate) FROM Account LIMIT 1

=============================================

OFFSET :

Use OFFSET to specify the starting row offset into the result set returned by your query.

Using OFFSET is helpful for paging into large result sets, in scenarios where you need to quickly jump to a particular subset of the entire results.

Syntax :

=============================================

SELECT fieldList FROM ObjectType [WHERE ConditionExpression] ORDER BY FieldOrderByList LIMIT number_of_rows_to_return OFFSET number_of_rows_to_skip

=============================================

For Example :

=============================================

SELECT Name FROM Marchandise__c WHERE price__c > 5.0 ORDER BY Name LIMIT 100 OFFSET 10

=============================================

GROUP BY :

With API version 18.0 and later, you can we use GROUP BY with aggregate functions, such as SUM() or MAX(), to summarize the data and enable you to rollup query results rather than having to process the individual records in your code.

Syntax :

=============================================

[GROUP BY FieldGroupByList]

=============================================

#1 Example :

=============================================

SELECT LeadSource,Count(Name) FROM Lead GROUP BY LeadSource

=============================================

#2 Example :

=============================================

SELECT LeadSorce FROM Lead GROUP BY LeadSource

=============================================

NOTE: You must use a GROUP BY clause if your query uses a LIMIT clause and an aggregated function.

Example :

=============================================

SELECT Name, Max(CreatedDate) FROM Account GROUP BY Name LIMIT 5

=============================================

GROUP BY ROLLUP :

This allows the query to calculate subtotals so you don't have to maintain that logic in your code.

Syntax :

=============================================

[GROUP BY ROLLUP ([fieldName1, fieldName2, fieldName3])]

=============================================

#1 Example : It Rolls the results up by one field

=============================================

SELECT LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP(LeadSource)

=============================================

#2 Example : It Rolls the results up by two fields

=============================================

SELECT Status , LeadSource, COUNT(Name) cnt FROM Lead GROUP BY ROLLUP (Status, LeadSource)

=============================================

HAVING :

With API version 18.0 and later, you can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM().

Syntax :

=============================================

[HAVING havingConditionExpression]

=============================================

#1 Example : To determine how many leads are associated with each LeadSource.

=============================================

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

=============================================

#2 Example : To generate more than 100 leads

=============================================

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource HAVING COUNT(Name) > 100

=============================================

#3 Example : It returns Account with duplicate names

=============================================

SELECT LeadSource , COUNT(Id) FROM Account GROUP BY Name HAVING COUNT(Id) > 1

=============================================

So these are the basic of salesforce SOQL queries with examples.

In Next EPISODE, we will discuss how to use relationship queries in salesforce SOQL in next part of this.

WOHOOO !! YOU HAVE JUST COMPLETED APEX SOQL BASICS PART 1 EPISODE

If you like this salesforcekid learning platform please let me know in the Comment section...Also, Share with your salesforce folks wish you
Happy learning ☁️⚡️ (Learn. Help. Share.)

<< PREVIOUS NEXT >>