Salesforce Object Query Language ( SOQL )

Salesforce Object Query Language ( SOQL )

 

Use the Salesforce Object Query Language (SOQL) to search your organization’s Salesforce data for specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed specifically for Salesforce data.

Salesforce Object Query Language (SOQL)은 사용하는 Org.의 데이터를 조회합니다.

SQL (Structured Query Language)의 SELECT 문장과 비슷하지만 Salesforce 전용으로 설계되어 있습니다.

 

SOQL를 사용하면 아래 환경에서 단순하면서 강력한 조회구문을 생성 할 수 있습니다.

• In the queryString parameter in the query() call
• In Apex statements
• In Visualforce controllers and getter methods
• In the Schema Explorer of the Force.com IDE

SOQL는 Join 구문, wildcards('scal%','_ing'), calculation expressions 지원하지 않습니다.

 

SOQL에서는 SELECT STATEMENT를 압축하여 steitment와 조합하여 사용하고 필요에 따라 정렬할 수 있는 변경 가능

SELECT one or more fields  FROM an object  WHERE filter statements and, optionally, results are ordered

예를 들어, 다음 SOQL 쿼리는 Name 값이 Sandy인 모든 거래처 레코드의 Id 및 Name 항목 값을 반환합니다.

SELECT Id , Name FROM Account WHERE Name = ' Sandy '

메모
Apex에서는 SOQL 스테이트먼트나 SOSL 스테이트먼트를 그 자리에서 사용하려면 각괄호로 둘러싸야 합니다.

앞에 콜론(:)이 있는 경우는 Apex 스크립트 변수와 식을 사용할 수 있습니다.

구문에 대한 자세한 내용은 "SOQL SELECT 구문"을 참조하십시오.

SOQL을 사용하는 케이스
데이터가 어떤 오브젝트에 존재하고 있는지를 인식하고 있으며 다음 조작을 할 경우에는 SOQL을 사용합니다.
1개의 오브젝트 또는 서로 관련된 복수의 오브젝트로부터 데이터를 취득한다.
지정된 조건을 만족하는 레코드의 수를 카운트한다.
질의 일부로서 결과를 정렬한다.
수치, 날짜 또는 체크 박스 항목에서 데이터를 가져온다.
메모

아카이브 데이터와 Big Object에서는 일부 SOQL 기능만 사용할 수 있습니다.자세한 내용은 "Big Object를 사용하는 SOQL"을 참조하십시오.

 

 

https://developer.salesforce.com/docs/atlas.ja-jp.210.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm

 

SOQL SELECT Syntax

SOQL query syntax consists of a required SELECT statement followed by one or more optional clauses, such as TYPEOF, WHERE, WITH, GROUP BY, and ORDER BY.

The SOQL SELECT statement uses the following syntax:

 

 

 

SELECT fieldList [subquery][...] [TYPEOF typeOfField whenExpression[...] elseExpression END][...] FROM objectType[,...] [USING SCOPE filterScope] [WHERE conditionExpression] [WITH [DATA CATEGORY] filteringExpression] [GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} [HAVING havingConditionExpression] ] [ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ] [LIMIT numberOfRowsToReturn] [OFFSET numberOfRowsToSkip] [FOR {VIEW | REFERENCE}[,...] ] [ UPDATE {TRACKING|VIEWSTAT}[,...] ]

 

Note

TYPEOF is currently available as a Developer Preview as part of the SOQL Polymorphism feature. For more information on enabling TYPEOF for your organization, contact Salesforce.

SyntaxDescription

fieldList subquery	Specifies a list of one or more fields, separated by commas, that you want to retrieve from the specified object. The bold elements in the following examples are fieldlists: SELECT Id, Name, BillingCity FROM Account SELECT count() FROM Contact SELECT Contact.Firstname, Contact.Account.Name FROM Contact You must specify valid field names and must have read-level permissions to each specified field. The fieldList defines the ordering of fields in the query results.fieldList can include a subquery if the query traverses a relationship. For example: The fieldlist can also be an aggregate function, such as COUNT() and COUNT(fieldName), or be wrapped in Translating Results.
typeOfField	A polymorphic relationship field in objectType or a polymorphic field in a parent of objectType that can reference multiple object types. For example, the What relationship field of an Event could be an Account, a Campaign, or an Opportunity. typeOfField cannot reference a relationship field that is also referenced in fieldList. See TYPEOF for more information.
whenExpression	A clause of the form WHEN whenObjectType THEN whenFieldList. You can have one or more whenExpression clauses inside a TYPEOF expression. See TYPEOF for more information.
elseExpression	A clause of the form ELSE elseFieldList. This clause is optional inside a TYPEOF expression. See TYPEOF for more information.
objectType	Specifies the type of object that you want to query(). You must specify a valid object, such as Account, and must have read-level permissions to that object.
filterScope	Available in API version 32.0 and later. Specifies the filterScope for limiting the results of the query.
conditionExpression	If WHERE is specified, determines which rows and values in the specified object (objectType) to filter against. If unspecified, the query() retrieves all the rows in the object that are visible to the user.
filteringExpression	If WITH DATA CATEGORY is specified, the query() only returns matching records that are associated with the specified data categories and are visible to the user. If unspecified, the query() returns the matching records that are visible to the user. The WITH DATA CATEGORY clause only filters objects of type: Question—to query questions. KnowledgeArticleVersion—to query articles. For more information about the WITH DATA CATEGORY clause, see WITH DATA CATEGORY filteringExpression.
fieldGroupByList	Available in API version 18.0 and later. Specifies a list of one or more fields, separated by commas, that are used to group the query results. A GROUP BY clause is used with aggregate functions to summarize the data and enable you to roll up query results rather than having to process the individual records in your code. See GROUP BY.
fieldSubtotalGroupByList	Available in API version 18.0 and later. Specifies a list of up to three fields, separated by commas, that are used to group the query results. The results include extra subtotal rows for the grouped data. See GROUP BY ROLLUP and GROUP BY CUBE.
havingConditionExpression	Available in API version 18.0 and later. If the query includes a GROUP BY clause, this conditional expression filters the records that the GROUP BY returns. See HAVING.
fieldOrderByList	Specifies a list of one or more fields, separated by commas, that are used to order the query results. For example, you can query for contacts and order the results by last name, and then by first name:

Note the following implementation tips:

• Statement Character Limit—By default, SOQL statements cannot exceed 20,000 characters in length. For SOQL statements that exceed this maximum length, the API returns a MALFORMED_QUERY exception code; no result rows are returned.
• Note

Long, complex SOQL statements, such as statements that contain many formula fields, can sometimes result in a QUERY_TOO_COMPLICATED error. The error occurs because the statement is expanded internally when processed by Salesforce, even though the original SOQL statement is under the 20,000 character limit. To avoid this, reduce the complexity of your SOQL statement.

•  
• Localized Results—SELECT statements can include the Translating Results, convertCurrency(), and FORMAT() functions in support of localized fields.
• Dynamic SOQL in Apex—Apex requires that you surround SOQL and SOSL statements with square brackets to use them on the fly. You can use Apex script variables and expressions when preceded by a colon (:).
• Ordered Results—The order of results is not guaranteed unless you use an ORDER BY clause in a query.
• Picklist Values—In API version 39.0 and later, query for picklist values by the value’s API name, which may differ from the actual value.