Getting Started with SOQL (2014)

Chapter 2. Basic SOQL Statements

This chapter will teach us the usage of the alias notation, logical operators, comparison operators, the IN operator, the NOT IN operator, the INCLUDES operator, and the EXCLUDES operator while building SOQL queries. The different types of operators available are mainly used for filtering the records retrieved via the SOQL query.

The WHERE clause usage for filtering the records will also be explained. We will also learn how to sort the retrieved records while querying using the ORDER BY clause. By using the ORDER BY clause, we will be sorting our fetched records in both ascending and descending order.

The alias notation

SOQL supports the alias notation. The alias notation in SOQL is usually used to distinguish different objects used in a single SOQL.

The name used for the alias notation is very important. The SOQL reserved keywords that cannot be used as alias names are AND, ASC, DESC, EXCLUDES, FIRST, FROM, GROUP, HAVING, IN, INCLUDES, LAST, LIKE, LIMIT, NOT, NULL, NULLS, OR, SELECT, WHERE, and WITH. Naming should be done in a way that denotes the object, which will help us when we write some complex SOQL statements.

Let us see a simple example to understand the usage of the alias notation in SOQL.

A sample query is given as follows:

SELECT Acct.Id, Acct.Name FROM Account Acct

In the preceding example, Acct is the alias notation for the Account object. We can directly fetch Id and Name of the Account object without using the alias notation as well. This query is just for understanding the usage of the alias notation. Further examples will be concerned more with objects in querying. We will get a clear picture about the usage of the alias notation in this chapter. The following screenshot shows the output of the SOQL execution:

So far, we are aware of using the alias notation in SOQL statements in Salesforce.com. With the help of the preceding example, we have queried records using the SOQL query from only one object. Let's see some complex examples to understand how the alias notation in SOQL statements work to distinguish different objects used in SOQL statements.

A sample query is given as follows:

SELECT FirstName, LastName FROM Contact Con, Con.Account Acct WHERE Acct.Name = 'Infallible'

In the preceding example, Acct is the alias notation for the Account object and Con is the alias notation for the Contact object. The alias notation will be very helpful to us while writing the SOQL queries for querying the records from multiple objects. Since many fields are common in all the objects, this alias notation helps us to distinguish among the objects used in the query. The following screenshot is the output of the SOQL execution:

The WHERE clause

The WHERE clause in SOQL is mainly used to filter retrieved data. The WHERE clause in SOQL is also called the condition expression. Whenever we want to filter our records from the objects using SOQL, we have to make use of the WHERE clause. The WHERE clause will retrieve the records that match the criterion or criteria. Followed by the WHERE clause, we can use the comparison operators, logical operators, IN operator, NOT IN operator, INCLUDES operator, EXCLUDES operator, and so on. We have the privilege of using a combination of these operators to filter correctly in a SOQL statement.

Let us see an example showing the usage of the WHERE clause. A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE FirstName != null

In the preceding example, the SOQL query will return all the Contact records where FirstName of the contacts is not null. The following screenshot is the output of the SOQL execution:

In the preceding example, we saw how to filter the null value records using the WHERE clause. In the same example, if we used the equals operator instead of the not equals operator, we would have retrieved records where the FirstName object of the contacts is null. With a small change, the query results differently. So, make sure to write your queries accurately. Let us see another example. A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE LastName = 'Bond'

In the preceding example, the SOQL query will return all the contacts where LastName is Bond. The preceding example with the condition LastName = 'BOND' will also produce the same result set since the SOQL string comparison is case insensitive. The following screenshot is the output of the SOQL execution:

The comparison operators

Comparison operators are used in SOQL to compare a value with another value to return true or false. While using comparison operators, we should be very careful with data types. We should not compare number values with strings. We have to make sure we are comparing the values with proper data to avoid warnings and errors in SOQL.

Let us see the comparison operators that can be used in SOQL:

Let's see some examples of these comparison operators in SOQL.

The equals operator

Using the equals operator, we can retrieve records that match the given criteria. The equals operator checks whether the values of two operands are equal. If the value is equal, the condition becomes true. We can make use of the equals operator if we know which value we have to compare with.

A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE LastName = 'Bond'

The preceding query will retrieve all the contacts where the last name of the contact is Bond. The following screenshot is the output of the SOQL execution:

The not equals operator

Using the not equals operator, we can retrieve records that do not match the given criteria. The not equals operator checks whether the values of two operands are equal. If the value is not equal, the condition becomes true. We can make use of the not equals operator to retrieve accurate data if we know the exact value that should not be included.

A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE LastName != 'Bond'

The preceding query will retrieve all the contacts where the last name of the contacts is not Bond. The following screenshot is the output of the SOQL execution:

The less than or equal to operator

Using the less than or equal to operator, we can retrieve records that are less than or equal to the given limit. The less than or equal to operator is used to check whether the value of the left operand is less than or equal to the value of the right operand. If yes, the condition becomes true. The record that matches the given limit will also be included in the result.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Amount <= 1000

The preceding query will retrieve all Opportunity instances where Amount is less than or equal to 1000. If the Amount object is exactly equal to 1000, those Opportunity instances are also included in the result. The following screenshot is the output of the SOQL execution:

If we want to avoid this, we have to use the less than operator instead of the less than or equal to operator. We should be very careful when choosing the operator. If we select the wrong operator, we will get incorrect results.

The less than operator

Using the less than operator, we can retrieve records that are less than the given limit. The less than operator is used to check whether the value of the left operand is less than the value of the right operand. If yes, the condition becomes true. The less than operator does not include records that match the given limit, unlike the less than or equal to operator. In the less than operator, the limit is not included in the result. But in the case of the less than or equal to operator, the limit is included in the result.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Amount < 10000

The preceding query will retrieve all Opportunity instances where Amount is less than 10000. The following screenshot is the output of the SOQL execution:

The Opportunity instances where Amount is exactly equal to 10000 are not included in the result. If we want to include this, we have to make use of the less than or equal to operator instead of the less than operator.

We need to ensure our symbol for the operator is correct, or else we will get incorrect results. If we are not careful, we may get the wrong set of data in our results. We have to check whether the symbol used is correct. A table with the list of operators and descriptions will help us a lot to avoid incorrect data while retrieving.

The greater than or equal to operator

Using the greater than or equal to operator, we can retrieve the records that are greater than or equal to the given limit. The greater than or equal to operator is used to check whether the value of the left operand is greater than or equal to the value of the right operand. If yes, the condition becomes true. The limit is also included in the result.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Amount >= 1000

The preceding query will retrieve all the Opportunity instances where Amount is greater than or equal to 1000. The Opportunity instances where the Amount object is exactly equal to 1000 are also included in the result. If we don't want to include those, we have to use the greater than operator instead. The choice of operator makes a big difference in the retrieved records count. The following screenshot is the output of the SOQL execution:

The greater than operator

Using the greater than operator, we can retrieve records that are greater than the given limit. The greater than operator is used to check whether the value of the left operand is greater than the value of the right operand. If yes, the condition becomes true.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Amount > 1000

The preceding query will retrieve all the Opportunity instances where Amount is greater than 1000. The following screenshot is the output of the SOQL execution:

The Opportunity instances where the Amount object is exactly equal to 1000 are not included in the results. If we want to include 1000 in our results, we have to use the greater than or equal to operator instead.

The LIKE operator

Using the LIKE operator, we can retrieve records that match the substring provided. The LIKE operator is mainly used to compare a value to all similar values using wildcard characters. The LIKE operator is also called the pattern matching filtering technique. Any record that matches the pattern alone will be retrieved, which improves the filtering.

A sample query is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Name LIKE 'Test%'

The preceding query will retrieve all the Opportunity instances where the name starts with Test. The following screenshot is the output of the SOQL execution:

The Opportunity instances whose names end with Test will not be included in the results. If we also want to include names ending with Test, we have to use another percentage symbol to the left so that it will be '%Test%'. When we do this, any Opportunity instance with Name that includes Test will also be included in the result. We have to use _ instead of % if we want to just match it with a single character to its left or right. The LIKE operator is very useful if we are unsure about the exact value with which we have to match. While using the LIKE operator, make sure you have entered the correct matching pattern. For example, the '%Test%' matching pattern will not be as efficient as 'Test%' due to the way indexes work and may take a longer time to retrieve the result set of a large object.

The IN operator

The IN operator is used to specify multiple values in the WHERE clause for matching and filtering records. The SOQL query will fetch records that match the values specified. The IN operator is mainly used to compare a value to a list of values that have been specified, and it retrieves the records if it matches the values specified in the list. The IN operator is used if you want to compare a value with multiple values to ensure the retrieved records are accurate.

A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE FirstName IN ('Rose', 'Sean', 'Jack', 'Test')

The preceding query will return all contacts where the first name matches the values specified. Here, the values inside the brackets are case insensitive for the IN operator. The following screenshot is the output of the SOQL execution:

The NOT IN operator

The NOT IN operator is used to specify multiple values in the WHERE clause for unmatching and filtering records. The SOQL will fetch records that do not match the values specified. The NOT IN operator is mainly used to compare a value to a list of values that have been specified, and it retrieves the records if it does not match the values specified in the list.

The NOT IN operator works in an opposite manner to the IN operator. It retrieves records that do not match the values specified, whereas the IN operator retrieves records that match the values specified.

A sample query is given as follows:

SELECT FirstName, LastName FROM Contact WHERE FirstName NOT IN ('Rose', 'Sean', 'Jack', 'Test')

The preceding query will return all the contacts where the first name does not match the values specified. The following screenshot is the output of the SOQL execution:

The result does not contain records that match the specified values.

The logical operators

The concept behind logical operators is simple and easy. Logical operators are mainly used to check multiple conditions in a single SOQL statement. Logical operators are connectors for connecting one or more conditions inside a single SOQL statement. It combines the conditions so that we can filter our records to be retrieved very accurately. Logical operator will return either true or false.

The two logical operators available in SOQL are the following:

· AND

· OR

The AND operator

Using the AND operator, we can retrieve records that satisfy all the conditions specified. If a record matches the first condition and does not match the second condition, the record will not be retrieved. For example, say the condition states that the city parameter should be equal to Chennai and the postal code parameter should not be null; if a record's city parameter is Chennai and postal code is null, the record will not be retrieved.

The OR operator

Using the OR operator, we can retrieve records that satisfy any one of the conditions specified. If a record matches the first condition and does not match the second condition, the record will be retrieved and vice versa. For example, say the condition states that thecity parameter should be equal to Chennai or Bangalore and if a record's city parameter is Mysore for a record, the record will not be retrieved. However, if the city parameter is Chennai for a record, that record will be included in the results.

The use of AND and OR together is allowed in SOQL to filter our records to avoid unwanted ones in our result. The use of AND and OR together helps us in many ways to fetch our required records for manipulation.

A sample query for the AND operator is as follows:

SELECT Name, Amount FROM Opportunity WHERE Amount > 1000 AND Amount < 100000

The preceding query will retrieve opportunities where Amount is greater than 1000 and less than 100000. The following screenshot is the output of the SOQL execution:

The Opportunity instances where amount is 1000 are not included in the result. If we want to include this, we have to use the greater than or equal to operator instead of the greater than operator.

A sample query for the OR operator is given as follows:

SELECT Name, Amount FROM Opportunity WHERE Name = 'Infallible' OR Name = 'Infallible Techie'

The preceding query will retrieve all the Opportunity instances where Name is Infallible or Infallible Techie. The following screenshot is the output of the SOQL execution:

The ORDER BY clause

The ORDER BY clause in SOQL is used to sort the records retrieved in the ascending or descending order.

An ascending order sample query is given as follows:

SELECT Name, Amount FROM Opportunity ORDER BY Name ASC

The preceding query will retrieve all the Opportunity instances arranged in the ascending order of the name. The following screenshot is the output of the SOQL execution:

A descending order sample query is given as follows:

SELECT Name, Amount FROM Opportunity ORDER BY Name DESC

The preceding query will retrieve all the Opportunity instances arranged in the descending order of the name. The following screenshot is the output of the SOQL execution:

Note

Here, ASC means ascending order and DESC means descending order. By default, it will always be ascending order.

The INCLUDES and EXCLUDES operators

The INCLUDES and EXCLUDES operators are mainly used for filtering the multipick list field in Salesforce.com. The standard way to filter multipick list field values in SOQL is using the INCLUDES and EXCLUDES operators. These operators are discussed in detail in Chapter 3, Advanced SOQL Statements with Examples.

Summary

In this chapter, we saw how to write basic SOQL statements in Salesforce.com. We started with a simple alias notation. We tried many examples to differentiate objects using alias notation.

Later, we saw the logical operators, comparison operators, the IN operator, and the NOT IN operator. The logical operators AND and OR were explained in detail. The comparison operators, =, !=, <, >, <=, >=, and LIKE, were also explained in detail. We learned where, when, and the ways in which these operators can be used in our SOQL statements in Salesforce.com.

We also covered how to sort retrieved records, and this was explained with sample queries using the ORDER BY clause.