Salesforce coding lessons for the 99%
Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
  • Beginner Tutorials
    • Apex
    • Certifications
    • Career Info
    • Technical Architect
    • Visualforce
    • Videos
  • Apex Academy
  • Success Stories
  • About Me
  • Misc
    • Mailbag
    • Challenges
    • Links
    • Login to my Org
Follow @dvdkliuor SUBSCRIBE!

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:

  1. The standard Phone field is a text field.
  2. 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!

Login to sfdc99 and try out these queries yourself!

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

66 Comments
Chris
March 16, 2021 @ 10:16 pm

Hi David! Thanks for the blog post, I certainly learned an item or two on my journey of learning how I can perform a SOQL query on Accounts that do not have a contact, in the related list, that is not marked “Yes” in our picklist field called “Primary Contact”.
Phrased another way… I’m trying to locate Accounts that do not have a Primary Contact, via “Yes” in their Primary Contact field, identified so we can work just those Accounts. Would you happen to have any insights? Thanks!

Reply
    David Liu
    March 16, 2021 @ 10:17 pm

    You’ll want the downwards SOQL query later in this chapter!

    Reply
chakri
May 30, 2020 @ 11:08 am

Hi David,

when I was started to learn about triggers, apex and soql
why did we want to use triggers in apex and soql used in triggers
and also please tell me how many ways we can use(triggers in apex and soql used in triggers) as like this

Reply
chakri
May 30, 2020 @ 11:04 am

Hi David,

when I was started to learn all these topics about triggers, apex and soql
i am confused because triggers can use in apex and soql can used in triggers
so, please give me clarity to which code languages are used in which language
as like triggers can used in apex
soql can used in triggers

Reply
chakri
May 30, 2020 @ 11:00 am

Hi David,

when I was started to learn all these topic about triggers apex and soql
i am confused because triggers can use in apex and soql can used in apex
so, please give me clarity to which code languages are used in which language

Reply
    David Liu
    May 30, 2020 @ 11:06 am

    Both are essential. =). SOQL is a lot easier, you can learn the basics in a day

    Reply
Madog
February 26, 2020 @ 3:28 am

Hi David ,
Just starting off on here but for the first query had to change Sex_c to regalray1__Sex__c for it to work.

Reply
    Anonymous
    February 26, 2020 @ 3:44 am

    All the custom fields have ‘regalray1_’ prefix in your SFDC99 LEARNER org.

    Can run last query by running the below :

    SELECT Id, Name, regalray1__Sex__c , BirthDate, regalray1__Likes_Ice_Cream__c
    FROM Contact
    WHERE
    BirthDate != TODAY
    AND regalray1__Sex__c = ‘Female’AND RecordTypeId != ‘012i0000000ES3H’
    AND regalray1__Weight__c> 100
    AND (
    ( Phone != null
    AND DoNotCall = false
    AND Phone LIKE ‘%(650)%’ )
    OR
    Email != null
    )
    ORDER BY Name ASC
    LIMIT 5

    Reply
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.

Reply
Robert
April 20, 2018 @ 11:46 am

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

Reply
    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.

    Reply
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

Reply
    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

    Reply
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.

Reply
    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!

    Reply
      Anonymous
      July 22, 2015 @ 11:07 pm

      Good to know! Thanks David!

      Reply
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

Reply
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.

Reply
    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

    Reply
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

Reply
    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()

    Reply
      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?

      Reply
        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! =)

        Reply
          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?

          Reply
            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!

            Reply
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?

Reply
    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.

    Reply
Mayank Srivastava
January 30, 2015 @ 5:40 pm

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

Reply
    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!!!

    Reply
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;
}
}

Reply
    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.

    Reply
    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!)

    Reply
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

Reply
    David Liu
    December 22, 2014 @ 4:38 pm

    Very confused on your requirement!

    Can you give an example?

    Reply
      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

      Reply
        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 =)

        Reply
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 :)

Reply
    David Liu
    December 20, 2014 @ 9:21 am

    Thank you for the kind words!

    Reply
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.

Reply
    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!!

    Reply
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

Reply
    David Liu
    August 21, 2014 @ 8:54 pm

    Thxxxxxxxxxxxx ^_^

    Reply
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.

Reply
    David Liu
    July 27, 2014 @ 9:39 pm

    Love you too bud!

    Reply
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?

Reply
    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.

    Reply
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

Reply
    David Liu
    June 24, 2014 @ 8:09 pm

    Try something like this =)

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

    Reply
    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.

    Reply
      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!

      Reply
Chaitanya
June 17, 2014 @ 8:16 pm

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

Reply
    David Liu
    June 17, 2014 @ 8:53 pm

    Hey you are awesome too!

    David

    Reply
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

Reply
    David Liu
    June 8, 2014 @ 8:21 pm

    Very kind of you hahaha!

    Reply
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!!

Reply
    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 =)

    Reply
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

Reply
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

Reply
    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

    Reply
      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

      Reply
        David Liu
        March 21, 2014 @ 3:23 pm

        No problem – I enjoy it!

        Reply
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

Reply
    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!!!!

    Reply
Anonymous
November 8, 2013 @ 3:13 am

Hi David,

Excellent explanation.

Thanks
Srikanth

Reply
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

Reply
    David Liu
    October 14, 2013 @ 6:39 pm

    Thanks Pranav, link is fixed! –David

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


*

*

Theme: Simple Style by Fimply