Home > RIA > Introduction to Salesforce.com Object Query Language (SOQL)

Introduction to Salesforce.com Object Query Language (SOQL)


I’ll assume for a moment that you are familiar with basic SQL for the purposes of this tutorial. If you are not, there are plenty of great resources on the web to brush up with. As a developer looking to extend Salesforce.com, SOQL is a pretty important and powerful aspect of coding. You can use SOQL to build your own custom query stings. These query strings can be used in the following places:

  1. Apex statements
  2. Visualforce getter methods and controllers
  3. In the queryString param passed in the query() call
  4. Finally, you can use the Schema Explorer in the Eclipse Toolkit (this one is pretty slick)

For those of you familar with SQL, you will find some differences but for the most part SOQL does just about everything you’ll need it to do.

SOQL uses the “SELECT” statement combined with any filter statements to bring back sets of data. The data sets returned may be optionally ordered as well (just like in SQL). Here is a basic example:

SELECT field1, field2, field3

FROM an object

WHERE filter statement(s) and (optionally) order the results

So, if you we want to get all the Leads from  your Salesforce.com account where the email address equals = “john.doe@somecompany.com” you would use the following SOQL statement:

SELECT ID, Name from Lead WHERE email = ‘john.doe@somecompany.com’

For a detailed documentation on the syntax of a SOQL SELECT statement, go to this Salesforce.com developer documentation page.

SOQL – COUNT()

Getting the “Count” of results being returned in a SOQL data set is pretty simple as well. For example, if I wanted to know how many Leads were going to be returned in my SELECT statement above, I can use the COUNT() function below:

SELECT COUNT() from Lead WHERE email = ‘john.doe@somecompany.com

SOQL Comparison Operators

Operator Common name
= Equals
!= Not equals
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal
IN In
NOT IN Not in (WHERE clause)
INCLUDES EXCLUDES Applies to multi-select picklists
LIKE Like (see section below)

For a full chart of all the comparison operators, check out this Salesforce.com developer documentation page.

SOQL – Like Operator

The LIKE operator provides a way to match partial text strings and includes support for wildcards. Let’s say for a moment we want to find all the Leads where the email domain is the same. For this, we can use a “LIKE” operator.  He is an example of a LIKE statement with the % wildcard.

SELECT Id, Name from Lead WHERE email  LIKE ‘%somecompany.com

The placement of the percent sign ‘%’ is key here. I am basically saying, bring me back all the Leads where the email ends with “somecompany.com”. Therefore I place the ‘%’ at the beginning of whatever I am looking for.” Anything to the left of the % sign is ignored in the search. If I didn’t know the full domain I could use the following statement:

SELECT Id, Name from Lead WHERE email  LIKE ‘%somecomp%

This is going to return all the leads where the email contains “somecomp”.

Other wildcard is the underscore “_”. Thing is used to match exactly one character.

Note: Unlike with SQL, the LIKE operator in SOQL performs a case-insensitive match.

SOQL – WHERE/OR

If you want to extend the WHERE clause to include multiple values, you can OR. See the example statement below:

SELECT ProductCode FROM PricebookEntry WHERE CurrencyIsoCode = ‘USD’ or CurrencyIsoCode = ‘GBP’

Taking it a step further, you can evaludate multiple things in the WHERE clause:

SELECT ProductCode,UnitPrice FROM PricebookEntry
WHERE (UnitPrice >= 10 and CurrencyIsoCode=’USD’)
OR (UnitPrice >= 5.47 and CurrencyIsoCode=’EUR’)

Advertisements
  1. March 17, 2013 at 9:45 AM

    Hey there! Do you use Twitter? I’d like to follow you if that would be okay. I’m absolutely enjoying your blog and look forward to new
    updates.

  2. May 23, 2013 at 12:59 PM

    Hi there just wanted to give you a quick heads up. The words in your content seem to be running off the screen in Ie.
    I’m not sure if this is a format issue or something to do with internet browser compatibility but I figured I’d
    post to let you know. The design and style look great
    though! Hope you get the issue fixed soon.
    Cheers

  3. May 29, 2013 at 7:42 PM

    Hey there! I am about to begin my own website and was wondering if you know where the best place to purchase a website url
    is? I am not even sure if that’s what its called? (I’m new to this)
    I’m referring to “https://thulasiramsoft.wordpress.com/2010/12/16/introduction-to-salesforce-com-object-query-language-soql/”. How do I go about obtaining one of these for the website I’m making?
    Thankyou

  4. May 31, 2013 at 9:22 AM

    You’re so cool! I don’t suppose I have read something like that before.
    So good to find another person with genuine thoughts on this topic.
    Seriously.. thanks for starting this up.
    This web site is one thing that’s needed on the web, someone with some originality!

  5. June 3, 2013 at 2:46 PM

    Hola! I’ve been reading your web site for a long time now and finally got the bravery to go ahead and give you a shout out from Dallas Texas! Just wanted to mention keep up the good job!

  6. June 7, 2013 at 8:02 AM

    Do you mind if I quote a couple of your posts as long as I provide credit and sources back to your
    webpage? My blog is in the exact same area of interest as yours and
    my users would genuinely benefit from a lot of the information you present here.
    Please let me know if this alright with you. Thanks a lot!

  7. July 18, 2013 at 5:56 AM

    When you brush your teeth immediately after eating,
    rinse your mouth, there is still more pus building up
    inside and around the groves and rough edges of the titanium screw.
    Dental laser tools are used in this procedure, many more people can
    now afford to have dental visits every six months.

  8. July 22, 2013 at 2:44 PM

    Hey! My name is Leroy and I just desired to say your blogging
    site rocks! It’s amusing simply because I use to have a web site that nearly had an identical url: https://thulasiramsoft.wordpress.com/2010/12/16/introduction-to-salesforce-com-object-query-language-soql/ mine was only a few characters different. Anyways, I’m a big supporter of your
    blog website and if you ever would like a guest write-up please make sure to email me
    personally at: leroywhitaker@gawab.com. I absolutely adore writing!

  9. August 18, 2013 at 11:06 PM

    Separation can be either the beginning of the end or a new beginning.
    This simple, but difficult to follow strategy is a must if you are to have any chance
    of getting your ex back. Learning the art of arguing is the foundation in which all
    good relationships can be built on.

  10. September 5, 2013 at 3:04 AM

    Take the time to learn how to properly apply the technique to pull this lift off
    by accessing the rest of my articles on the matter for free.
    In case you are doing some form of workout, then you will understand that interval training will be much better for burning fat
    rather than lengthy, steady-state, boring, cardio on the TV.
    The Snatch is one of the best fat burning exercise choices available today.

  11. September 10, 2013 at 9:27 PM

    Instead of eating three large meals, you should eat six or seven smaller meals to reduce your belly fat.
    The more fit that you are, the more you will have to exert yourself in order to burn fat.

    Best of all, it can be a low impact exercise that will not stress
    out your joints or muscles if done properly.

  12. September 25, 2013 at 9:20 AM

    It’s remarkable in favor of me to have a website, which is beneficial designed for my experience.

    thanks admin

  13. February 26, 2014 at 12:05 AM

    I havе loaɗed your bloց in 4 completely Ԁifferent browsers and I
    must say this website loads a lot quicker then most.
    Would you mind contacting mе the name of your hosting company?
    Ӎу personal e-mail is: ρenniterpstra@gmail.com.
    Ӏ will even sign up through your affiliate link іf you’d like.
    Bless you

  14. March 2, 2014 at 7:43 AM

    Hi аre using Wordpresѕ for your blog platform?
    I’m ոew to the blοg world but I’m trying to get started and creɑte mʏ own.
    Do you need any html coding expertise to make
    your owո blog? Any help would be greatly aƿpreciated!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: