Example: How to write a simple SOQL query

June 7, 2013

Preface: this post is part of the SOQL: A Beginner’s Guide series.

SOQL is so basic I almost don’t need to explain these examples!

The barebones query:

SELECT Id, Name, Sex__c, BirthDate FROM Contact

This is your basic query that pulls three standard fields and one custom field from every contact.

The WHERE clause:

SELECT Id, Name, Sex__c FROM Contact WHERE DoNotCall = false

You’ll learn to love the WHERE clause – you’ll almost always use one!

Using OPERATORs:

SELECT Id, Name, Sex__c FROM Contact WHERE Phone != null

!= is an operator that simply means “not equal to”.

Using AND and OR in the WHERE clause:

SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c
    FROM Contact 
    WHERE Phone != null
        AND DoNotCall = false

SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c 
    FROM Contact
    WHERE ( Phone != null AND DoNotCall = false )
        OR Email != null

Notice the use of parenthesis when using multiple AND’s and OR’s! Also – spacing doesn’t matter.

Using TEXT, PICKLIST, or ID values:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Phone != null
        AND Sex__c = 'Female'
        AND RecordTypeId != '012i0000000ES3H'

Always wrap those three data types in single quotes! That record type is for married contacts…

Using Numbers:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Phone != null
        AND Sex__c = 'Female'
        AND Weight__c > 100

Let’s promote healthy eating guys! Notice how we didn’t use quotes for the number.

Using Dates:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Phone != null
        AND BirthDate = TODAY

TODAY is a special date keyword that makes working with date fields much easier!

Fuzzy Matching:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Sex__c = 'Female'
        AND Phone LIKE '%(650)%'

Two things to note here:

The standard Phone field is a text field.
The % is a SOQL wildcard that matches zero or more characters.

Sorting the results:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Phone != null
    ORDER BY Name ASC

ASC stands for ascending. Just one of your many sorting options!

Limiting your results:

SELECT Id, Name, Phone, Birthdate, Likes_Ice_Cream__c
    FROM Contact    
    WHERE Phone != null
    LIMIT 5

This simply enforces a max of five records, even though the query might usually return thousands.

Putting it all together:

SELECT Id, Name, Sex__c, BirthDate, Likes_Ice_Cream__c 
    FROM Contact
    WHERE
        BirthDate = TODAY
        AND Sex__c = 'Female'
        AND RecordTypeId != '012i0000000ES3H'
        AND Weight__c > 100
        AND (
            ( Phone != null
              AND DoNotCall = false
              AND Phone LIKE '%(650)%' )
            OR
            Email != null
        )
    ORDER BY Name ASC 
    LIMIT 5

Let’s hope this query gets some lucky guy a date!

Next post: Example: How to write a cross-object SOQL query (“upwards”)

58 Comments

abhishek

February 13, 2019 @ 10:51 pm

Hey David,

would you please tell me how to access picklist values (not label or API name) from our Salesforce org through SOQL query. I have searched and found that one should use “Schema.describeFieldResult”. It would be really awesome if SOQL can do that.

Thanks in advance for your help.

Robert

April 20, 2018 @ 11:46 am

Can a Select * (list ALL columns) from tablename be done using SOQL?

David Liu

April 20, 2018 @ 12:12 pm

Sadly, no. There are ways to hack this but it involves Apex. Basically you get a list of all possible fields, then include them into your query.

Ankita

July 29, 2016 @ 5:57 am

Hi David,

Could you please tell why an IN has been used in this query

Map accounts = new Map(
[select id, name, billingState from Account where id in :accountIds]);

Thanks
Ankita

David Liu

July 29, 2016 @ 1:26 pm

So the accountIds variable is probably a list or a set of IDs.

Using the IN in the way you’ve written is a shortcut for writing this:

SELECT Id, Name, BillingState
FROM Account
WHERE Id = ‘theFirstIdInTheList’
OR Id = ‘theSecondIdInTheList’
OR Id = ‘theThirdIdInTheList’
etc etc etc

Hope this helps!
David

Malik

July 14, 2015 @ 10:27 pm

Hi David,

I know this post is older now, but it seems like it may be better to use the record type name versus the recordtypeid as that id will change between environments like sandbox and prod I believe.

David Liu

July 14, 2015 @ 10:30 pm

The record type IDs would actually be the same across environments! Especially if you use Change Sets to create new ones!

Anonymous

July 22, 2015 @ 11:07 pm

Good to know! Thanks David!

MASOOD

April 3, 2015 @ 12:23 am

public class SimpleLogic{
public static void myTest(){

List acc = new List([select name ,(select name from contacts) from account limit 10]);

for(Account acct : [SELECT Name, (SELECT Name FROM Contacts) FROM Account]) {
// Integer count=0;
for (Contact c : acct.Contacts) {
// count++;

system.debug(c);

}

}
}
}

HOW CAN I PASS THIS CONTACT TO ANOTHER METHOD TELL ME ANSWER

Roy

February 27, 2015 @ 8:49 am

If you wanted to write a query tied to a date field that is not a birthday, but was of annual importance, like an anniversary. Is there a “TODAY” option that works for that? I’ve noticed that the birthdate field seems to be special in that regard.

David Liu

February 28, 2015 @ 4:27 pm

There sure is =)
http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm

Dylan K

February 18, 2015 @ 6:15 pm

Hi David,

Thanks for this; it’s a great quick summary.

I want to extract data from SFDC to store in another database for some analysis; I would like to include the current date in the extraction, but it seems that the TODAY keyword can only be used in a filter condition. Is there any way to do a query like this that will include the current date (or datetime) in the output?

SELECT Id,IsDeleted,MasterRecordId,Name,Type,ParentId,…..,TODAY FROM Account

Obviously, it doesn’t like TODAY or TODAY()… Haven’t been able to find anything in Google…

Thanks!
Dylan

David Liu

February 18, 2015 @ 8:22 pm

Not available in SOQL but people would normally do this on the Apex side!

In Apex you can do a SOQL query, then stamp the time using Date.today() or DateTime.now()

Dylan K

February 18, 2015 @ 8:26 pm

That’s what I was starting to figure. So, with Apex, would we be able to have every record tagged with the current date right before midnight and then I could do my extraction just after?

David Liu

February 18, 2015 @ 8:30 pm

Actually that’s a very interesting point!

You can make a formula field on the object that is simply NOW(), then make sure to include that field in your query.

The Apex way I was thinking of was much harder – this idea that you basically came up with is much better! =)

Dylan K

February 18, 2015 @ 8:36 pm

Saying I came up with it is very generous… Now, I also will eventually need to have every record also tagged with the OrgID, and I can’t seem to refer to it in the select statement, so I suppose I could probably do the same thing – create a formula field on the object that will return OrgID and then reference that field in the SOQL query?

David Liu

February 18, 2015 @ 8:46 pm

That one might be a bit more difficult! Don’t know a good way off the top of my head to do it, but you should know that the ID never changes so you might be able to just save it somewhere else!

Amit Vaidya

February 3, 2015 @ 2:10 am

Hi David,

How can I get second highest salary from Employee table using SOQL in Salesforce?

“Select max(salary) from employee” will give max salary but i need second highest salary using single query and single record output. How can i get it?

David Liu

February 3, 2015 @ 9:23 pm

Don’t aggregate the SOQL query, but sort it by Salary DESC. Then get myEmployees[1] to get the second one.

Mayank Srivastava

January 30, 2015 @ 5:40 pm

David,
Under Fuzzy Matching , you say ‘Three things to note here’ but list only two ;)

David Liu

February 2, 2015 @ 8:29 pm

LOL!!!

Updated!

You have no idea how much I appreciate these kind of comments. Typos drive me NUTS and I’d rather someone tell me than finding out the hard way months later!!! THANK YOU Mayank!!!

Graham Milne

December 29, 2014 @ 7:07 am

Hi David,

I am having some trouble with an SOQL Query and was hoping you could shed some light on what I may be doing wrong. I have created an email service and apex class to handle inbound emails. The idea is to mimic email to case functionality for my custom object. The apex class creates a task when the email subject matches a custom field on my custom object. This is working fine at the moment but the problem is I need to check if the email subject contains the value of my custom field rather than is equal to it.

So Basically I need it to check

where custom field contains email.subject

instead of

where custom field = email.subject

I have tried using a combination of wildcards and the Like Operator but cannot figure it out. I have posted my Apex Class code below.

The frustrating thing is I am so close as it works when the subject is an exact match. I just can’t figure out how to use the Like operator correctly or if that is even the correct operator to be using.

Any help would be appreciated.

Thanks for all your efforts on the site. It has been a massive help to me.

global class CreateTaskEmailExample1 implements Messaging.InboundEmailHandler {

global Messaging.InboundEmailResult handleInboundEmail(Messaging.inboundEmail email,
Messaging.InboundEnvelope env){

// Create an InboundEmailResult object for returning the result of the
// Apex Email Service
Messaging.InboundEmailResult result = new Messaging.InboundEmailResult();

String myPlainText= ”;

// Add the email plain text into the local variable
myPlainText = email.plainTextBody;

// New Task object to be created
Task[] newTask = new Task[0];

// Try to look up any Request based on the email subject

Request__c vCon;

try {
vCon = [SELECT Id, Name, Number__c, Test__c
FROM Request__c
WHERE Test__c = :email.subject];
//LIMIT 1];

// Add a new Task to the Request record we just found above.
newTask.add(new Task(Description = myPlainText,
Priority = ‘Normal’,
Status = ‘Inbound Email’,
Subject = email.subject,
IsReminderSet = false,
WhatId = vCon.Id));

// Insert the new Task
insert newTask;

System.debug(‘New Task Object: ‘ + newTask );
}
// If an exception occurs when the query accesses
// the Request record, a QueryException is called.
// The exception is written to the Apex debug log.
catch (QueryException e) {
System.debug(‘Query Issue: ‘ + e);

}

// Set the result to true. No need to send an email back to the user
// with an error message
result.success = true;

// Return the result for the Apex Email Service
return result;
}
}

Graham Milne

December 29, 2014 @ 7:09 am

This is the portion I am having trouble with

try {
vCon = [SELECT Id, Name, Number__c, Test__c
FROM Request__c
WHERE Test__c = :email.subject];
//LIMIT 1];

Thanks.

David Liu

December 29, 2014 @ 12:52 pm

This one may be better to post on the SFDC99 forums as it’s a bit more involved!

(Also, try to use the code tags so it formats nicely there!)

Tabrez Ansari

December 22, 2014 @ 1:08 am

Hi David

I’m working on creating custom workflow
I’m trying to get multiple records of single rule into a single how can i get it
for eg
I’ve created a new “Rule name’ & i’ve given 2 rule criteria so how can i get those records into a single string
please guide me how to do it
if not too much please give an example as well

Thanks in advance

David Liu

December 22, 2014 @ 4:38 pm

Very confused on your requirement!

Can you give an example?

Tabrez Ansari

December 22, 2014 @ 9:26 pm

Hi David

In my first page I have used getDescribe() function to get all the object of the org & I’m passing that selected object to another page using map where I will get the list of related fields of that object. On another page I have 5 rows as we have in standard workflow for rule criteria
When I’m selecting the field, operator & entering the value & saving it, each row is getting saved as a single record, by appending these fields using formula field example. “accountnumber = 123456” (Formula field of text type).
I’ve created a master detail relationship between the 2 custom objects “Rule” & “Rule Criteria”. The records of the Rule criteria are appearing under the appropriate ‘Rule Name’ related list.
Now my requirement is I need to fetch the records under ‘Rule Name’ one by one & from the object “Rule Criteria” & append them in a single string separating them by AND , example

This is how my page look like

Object : Account (output label for getting the selected object from the previous page)
Rule Name : My Example Rule (Text Field to enter rule name)
Evaluation Criteria : Record Is Created (Dropdown List)

Field Name Operator Value
Account Number Equals 123456
Email Contains example
Select Field None
Select Field None
Select Field None

Now the records in the object will look like
Record 1
Rule Name : My Example Rule
Field : accountnumber
Operator : =
Value = 123456
Formula String : accountnumber = 123456

Record 2
Rule Name : My Example Rule
Field : email
Operator : LIKE \’%{val}%\ (i.e contains)
Value = test
Formula String : email LIKE \’%{val}%\ test

So the query will look like “ String str = select accountnumber, email from account where Record1.formula_string AND Record2.formula _string; “
So how can I get the functionality of the operators selected &
How can I write the string
Please assist

David Liu

December 23, 2014 @ 12:02 am

On second thought this is too much code for me to process, sorry!!

Try asking the forums on this site =)

Pinaki

December 20, 2014 @ 9:07 am

Hi David,
I am a beginner in salesforce and just trying to learn from your materials.
Loving to read it. Hope I will learn it quickly with your help.
Thanks a lot.
You are masterpiece :)

David Liu

December 20, 2014 @ 9:21 am

Thank you for the kind words!

Sindoora (@GSindoora)

November 10, 2014 @ 10:48 pm

Hi David,

I observe the API request were increasing,while querying using workbench.
At the same time if we write query in the developer console will it fall under API request.

David Liu

November 11, 2014 @ 10:04 am

Workbench uses the API so it will take up API calls!

It’s a daily limit so it’ll refresh the next day. The limit is relatively high so you must be using Workbench A LOT!!

Nag

August 21, 2014 @ 6:42 am

Hi David I am a Big Fan For youuuuuuuuuuu, nice job thank you so much david i learned so much from this website

David Liu

August 21, 2014 @ 8:54 pm

Thxxxxxxxxxxxx ^_^

srikanth

July 25, 2014 @ 11:59 am

Hi David,
I dont have words to praise you buddy.The way of your explanation is Awesome.

A BiG tHaNkS tO yOu.

SrIk@NtH.

David Liu

July 27, 2014 @ 9:39 pm

Love you too bud!

Vijay

July 17, 2014 @ 9:32 pm

Hi David,
Can you give me example for how to query all the fields of a sObject at a time?

David Liu

July 17, 2014 @ 10:03 pm

In Salesforce there is no SELECT * since you shouldn’t be querying all fields (it will slow down your code and you’ll hit governor limits)!

It’s best to list them out, even if it’s many fields! This way you know your code will not break if an admin suddenly adds many fields.

Halim

June 24, 2014 @ 6:48 am

Hi
I’m trying to do this :
and (A OR B) and C but it doesn’t work.

RollUpSummaryUtility.rollUpTrigger(fieldDefinitions,Affaire,
‘Affaire__c’, ‘Mission_affaire__c’,’Mission__c’, ‘and Type_d_affaire__c = \’Dossier\’ or Type_d_affaire__c = \’PEC\’ and Etape__c = \’Gagnée\”);

Can you help me.

Thanks

David Liu

June 24, 2014 @ 8:09 pm

Try something like this =)

((x + y > 2) OR (a + b = 3)) AND (c – d < 5)

Halim

June 25, 2014 @ 3:08 am

Hi
thank you for your help. I’ve try what you tell me, with the different field:
RollUpSummaryUtility.rollUpTrigger(fieldDefinitions1, Affaire,// trigger.new,
‘Affaire__c’, ‘Mission_affaire__c’,’Mission__c’, ‘(( Type_d_affaire__c = \’Dossier\’)or( Type_d_affaire__c = \’PEC\’) and Etape__c \’Annulée\’ and Etape__c \’Perdue\’ and Etape__c \’Gagnée\”);

I’ve this error:
MissionRollUpAffairePECTEST: execution of AfterUpdate caused by: System.QueryException: unexpected token: ‘(‘: Class.RollUpSummaryUtility.rollUpTrigger: line 50, column 1

I think my RollupSummaryUtility doesn’t like the filter.
Perhaps I didn’t have the choice of doing the same trigger with my new type of opportunity(affaire)

Thank you very much for your time.

David Liu

June 25, 2014 @ 11:53 am

Not familiar with there’ll up code you’re using but it looks like you just have some issues with where you’re placing your parenthesis and single quotes. Double check these and your code will work!

Chaitanya

June 17, 2014 @ 8:16 pm

U r so cool David very smart work from you Thanks:)

David Liu

June 17, 2014 @ 8:53 pm

Hey you are awesome too!

David

Anonymous

June 6, 2014 @ 9:41 pm

DAVID u are really a good tutor…..u just know how to begin the things for a beginner and make them sound on the topics…….kudos to u man……thanks a lot from my side…hope a lot of work like this from u…have a long life

David Liu

June 8, 2014 @ 8:21 pm

Very kind of you hahaha!

Steven

May 20, 2014 @ 2:46 pm

David,

Your site has been invaluable for my first foray into apex. Thanks!

I have created a trigger that essentially creates an autonumber by record type. Using SOQL I get the records by record type and order them such that the largest number is in list position [0]. Works great except the related list Name field is text and on the SOQL order it only sorts by string not number. Thus if there is a string such as 3000000 and the number 9, the order considers the 9 the larger value. I tried using valueOf(Name) to no avail. I prefer to be able to overwrite the name with the new number and thus prefer not to create an additional number field just for the ordering. Is there a solution to this issue?

Thanks again!!

David Liu

May 20, 2014 @ 5:43 pm

You can simply create a formula field that converts the text string into a number, then sort by that field =)

Virginia

April 25, 2014 @ 12:02 pm

“Let’s hope this query gets some lucky guy a date!”

Uh…let’s hope not! : )

WHERE
BirthDate = TODAY

Joey Len

March 21, 2014 @ 8:54 am

Morning David,

LOVE your tutorials. For a complete newb they read simple and straightforward. I do have a question about a trigger I am trying to write. I have an object, Territory Plan, and I need to capture:

YTD Sales by rep
Count of opportunities in each rep’s pipeline
Pipeline by rep
Factored pipeline by rep

I took your suggestion and used Workbench to write the SOQL code which formed perfect:

SELECT Amount FROM Opportunity WHERE StageName != ‘closed won’ AND StageName != ‘closed lost’ AND StageName != ‘scheduled’ AND StageName != ‘shipped’ AND StageName != ‘demo’ ORDER BY Territory_Name__c ASC NULLS FIRST

but I am trying to figure out how to get the totals I need. Do I aggregate the results? Can I total them by rep? Where will the results end up? Am I even barking up the right tree?

If you could just let me know if I am headed in the right direction.
Thanks in advance,

Joey

David Liu

March 21, 2014 @ 1:33 pm

Excellent job Joey and I’m glad you like the site!

There are two ways to do this:

1. Loop through all the results and total the amounts individually:
https://www.sfdc99.com/2013/10/06/loops-for-and-foreach-loops/

2. Use a GROUP BY function in your SOQL query (by rep) to aggregate the data (best method):
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_agg_functions.htm

These queries should happen directly inside your trigger – read on to at least Chapter 4 to see exactly how to combine SOQL and Apex!

David

Joey Len

March 21, 2014 @ 2:32 pm

David,

Thanks so much for taking the time to respond to my question and confirm that my initial thoughts were in the right direction.

Joey

David Liu

March 21, 2014 @ 3:23 pm

No problem – I enjoy it!

Anonymous

January 14, 2014 @ 7:51 pm

Dlo, i’ll have you know that I searched Google for “salesforce SOQL fuzzy select” and I saw sfdc99 as the top hit. =)
-Pdro

David Liu

January 14, 2014 @ 7:58 pm

Sweet!!! My SEO sucks and I spend zero time on that so I am happy to hear this hahaha.

Keep on truckin PDro!!!!

Anonymous

November 8, 2013 @ 3:13 am

Hi David,

Excellent explanation.

Thanks
Srikanth

Pranav

October 14, 2013 @ 1:21 am

Hi David,

Simple explanation with some good examples.. The “Sorting Options” link appears to be a bad link..

Pranav

David Liu

October 14, 2013 @ 6:39 pm

Thanks Pranav, link is fixed! –David

Example: How to write a simple SOQL query

Leave a Reply Cancel reply