LIKE operator:
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 operator is supported for string fields only.
The LIKE operator performs a case-sensitive match for case-sensitive fields, and a case-insensitive match for case-insensitive fields.
For example, the following query matches Orange, Orang, and Ora, but not Oora:
SELECT AccountId, FirstName, lastname
FROM Contact
WHERE lastname LIKE 'Ora%'
IN operator:
The values for IN must be in parentheses. String values must be surrounded by single quotes.
SELECT Name FROM Account
WHERE BillingState IN ('California', 'New York')
NOT IN operator:
If the value doesn’t equal any of the values in a WHERE clause. For example:
SELECT Name FROM Account
WHERE BillingState NOT IN ('California', 'New York')
The values for NOT IN must be in parentheses. String values must be surrounded by single quotes.
INCLUDES EXCLUDES operator:
Applies only to multi-select picklists.
Semi-Joins with IN and Anti-Joins with NOT IN:
A) ID field Semi-Join
You can include a semi-join in a WHERE clause. For example, the following query returns account IDs if an associated opportunity is lost:
SELECT Id, Name
FROM Account
WHERE Id IN
( SELECT AccountId
FROM Opportunity
WHERE StageName = 'Closed Lost'
)
B) ID field Anti-Join
The following query returns account IDs for all accounts that don’t have any open opportunities:
SELECT Id
FROM Account
WHERE Id NOT IN
(
SELECT AccountId
FROM Opportunity
WHERE IsClosed = false
)
The following query returns opportunity IDs for all contacts whose source isn’t Web:
SELECT Id
FROM Opportunity
WHERE AccountId NOT IN
(
SELECT AccountId
FROM Contact
WHERE LeadSource = 'Web'
)
You can’t query on the same object in a subquery as in the main query.
SELECT Id, Name
FROM Account
WHERE Id IN
(
SELECT ParentId
FROM Account
WHERE Name = 'myaccount'
)
You can’t use subqueries with OR.
COUNT, FOR UPDATE, ORDER BY, and LIMIT aren’t supported in subqueries.
ORDER BY:
Order BY clause is used to get results in particular order.
SELECT Name, Industry FROM Account ORDER BY Name
If there a multiple Account records with the same Name,the order of the results for the above query can vary.
So we can add the Id (or any other field that is unique in the results) to the ORDER BY clause.
For example:
SELECT Name, Industry FROM Account ORDER BY Name, Id
ASC or DESC:
Specifies whether the results are ordered in ascending (ASC) or descending (DESC) order. Default order is ascending.
NULLS FIRST or NULLS LAST:
Orders null records at the beginning (NULLS FIRST) or end (NULLS LAST) of the results.
By default, null values are sorted first.
For example, the following query returns a query result with Account records in alphabetical order by first name,
sorted in descending order, with accounts that have null names appearing last:
SELECT Name
FROM Account
ORDER BY Name DESC NULLS LAST
OFFSET:
Here are a few points to consider when using LIMIT and OFFSET in your queries:
The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.
OFFSET is intended to be used in a top-level query, and is not allowed in most subqueries, so the following query is
invalid and returns a MALFORMED_QUERY error.
SELECT Name, Id
FROM Account
WHERE Id IN
(
SELECT AccountId
FROM Opportunity
LIMIT 100
OFFSET 20
)
ORDER BY Name
A subquery can use OFFSET only if the parent query has a LIMIT 1 clause. The following query is a valid use of OFFSET in a subquery:
SELECT Name, Id,
(
SELECT Name FROM Opportunities LIMIT 10 OFFSET 2
)
FROM Account
ORDER BY Name
LIMIT 1
OFFSET cannot be used as a subquery along with WHERE clause, even if the parent query uses LIMIT 1.
No comments:
Post a Comment