A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See Join Queries section below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
This section discusses some of the features and restrictions that are specific to how the connector supports joins. If possible, the connector to performs joins server side. Joins that Salesforce cannot process are performed client side when SupportEnhancedSQL is set to true. The connector for Salesforce supports server-side joins based on Salesforce Object Query Language (SOQL). The connector supports standard SQL syntax instead of proprietary SOQL to allow easy integration with a wide variety of SQL tools. Join queries in Salesforce are based on the relationships among Salesforce objects.
Salesforce objects can be linked using relationships. The standard Salesforce objects have predefined relationships. You can define relationships for your custom objects.
Salesforce relationships are directional and are of the following types: one-to-many (parent to child) or many-to-one (child to parent). Since the relationships are directional the order in which the tables are included in the query determines the path of relationship traversal.
The following query shows a simple parent-to-child join query. This query returns all Accounts and the first and last name of each Contact associated with that Account.
Salesforce relationships can be polymorphic. That is, a given relationship on a field can refer to more than one type of entity. For example, the Task entity contains a Who relationship, which by default may refer to a Contact or Lead.
The following query shows a join based on a polymorphic relationship. To return only Tasks referring to a Contact on the Who relationship, specify the type of entity in the relationship:
You can specify a join condition that is a custom relationship. The following query retrieves the names of all Account records and the first names of all Contacts that match the specified join condition:
You can use the syntax detailed below to execute joins on Salesforce objects that are processed by the Salesforce servers. Joins on related objects are processed on the server. A benefit of predefined relationships is that you do not need to specify the join conditions to execute a join with Salesforce data; the conditions are already accounted for based on the relationship.
The following query returns the first names of all the Contacts in the organization and for each Contact the name of the parent Account associated with that Contact.
If there are multiple relationships between the tables, you can explicitly set the join criteria. The following query matches on a custom relationship determined by the columns in the join criteria (Account.MyCustomColumn__c and Contact.Id), instead of the default parent-to-child relationship between Accounts and Contacts:
Salesforce supports inner joins. The following query retrieves all Account records that are associated with an Opportunity:
The following query shows a join between three custom objects. This query joins the custom tables NW_Product__c, NW_Category__c, and NW_Suppliers__c and returns the Name field from each of them. Additionally, the results from the NW_Category__c table are filtered for names that start with "Dairy":
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns the currency field converted to the user's currency
column: Any column expression.
Returns a number representing the calendar month of a date field (1 for January, 12 for December).
column: Any column expression.
Returns a number representing the calendar quarter of a date field (1 for January 1 through March 31, 2 for April 1 through June 30, 3 for July 1 through September 30, 4 for October 1 through December 31).
column: Any column expression.
Returns a number representing the calendar year of a date field (2009).
column: Any column expression.
Returns a number representing the day in the month of a date field (20 for February 20).
column: Any column expression.
Returns a number representing the day of the week for a date field (1 for Sunday, 7 for Saturday).
column: Any column expression.
Returns a date representing the day portion of a dateTime field (32 for February 1).
column: Any column expression.
Returns a date representing the day portion of a dateTime field (2009-09-22 for September 22, 2009).
column: Any column expression.
Returns a number representing the fiscal month of a date field. This differs from CALENDAR_MONTH() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in March: 1 for March, 12 for February.
column: Any column expression.
Returns a number representing the fiscal quarter of a date field. This differs from CALENDAR_QUARTER() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in July: 1 for July 15, 4 for June 6.
column: Any column expression.
Returns a number representing the fiscal year of a date field. This differs from CALENDAR_YEAR() if your organization uses a fiscal year that does not match the Gregorian calendar (2009).
column: Any column expression.
Returns a number representing the hour in the day for a dateTime field (18 for a time of 18:23:10).
column: Any column expression.
Returns a number representing the week in the month for a date field (2 for April 10). The first week is from the first through the seventh day of the month.
column: Any column expression.
Returns a number representing the week in the year for a date field (1 for January 3). The first week is from January 1 through January 7.
column: Any column expression.
Returns the currency field converted to the user's currency
column: Any column expression.
Returns a number representing the calendar month of a date field (1 for January, 12 for December).
column: Any column expression.
Returns a number representing the calendar quarter of a date field (1 for January 1 through March 31, 2 for April 1 through June 30, 3 for July 1 through September 30, 4 for October 1 through December 31).
column: Any column expression.
Returns a number representing the calendar year of a date field (2009).
column: Any column expression.
Returns a number representing the day in the month of a date field (20 for February 20).
column: Any column expression.
Returns a number representing the day of the week for a date field (1 for Sunday, 7 for Saturday).
column: Any column expression.
Returns a date representing the day portion of a dateTime field (32 for February 1).
column: Any column expression.
Returns a date representing the day portion of a dateTime field (2009-09-22 for September 22, 2009).
column: Any column expression.
Returns a number representing the fiscal month of a date field. This differs from CALENDAR_MONTH() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in March: 1 for March, 12 for February.
column: Any column expression.
Returns a number representing the fiscal quarter of a date field. This differs from CALENDAR_QUARTER() if your organization uses a fiscal year that does not match the Gregorian calendar. If your fiscal year starts in July: 1 for July 15, 4 for June 6.
column: Any column expression.
Returns a number representing the fiscal year of a date field. This differs from CALENDAR_YEAR() if your organization uses a fiscal year that does not match the Gregorian calendar (2009).
column: Any column expression.
Returns a number representing the hour in the day for a dateTime field (18 for a time of 18:23:10).
column: Any column expression.
Returns a number representing the week in the month for a date field (2 for April 10). The first week is from the first through the seventh day of the month.
column: Any column expression.
Returns a number representing the week in the year for a date field (1 for January 3). The first week is from January 1 through January 7.
column: Any column expression.
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM Account
SELECT [Name] AS MY_Name FROM Account
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Account
SELECT * FROM Account WHERE Industry = 'Floppy Disks'
SELECT COUNT(*) AS MyCount FROM Account
SELECT COUNT(DISTINCT Name) FROM Account
SELECT DISTINCT Name FROM Account
SELECT Name, MAX(AnnualRevenue) FROM Account GROUP BY Name
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT BillingState, Name FROM Account ORDER BY Name ASC
SELECT BillingState, Name FROM Account LIMIT 10
SELECT * FROM Account WHERE Industry = @param
SELECT COUNT(*) FROM Account WHERE Industry = 'Floppy Disks'
SELECT COUNT(DISTINCT BillingState) AS DistinctValues FROM Account WHERE Industry = 'Floppy Disks'
SELECT Name, AVG(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT MIN(AnnualRevenue), Name FROM Account WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT Name, MAX(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'
GROUP BY Name
SELECT SUM(AnnualRevenue) FROM Account WHERE Industry = 'Floppy Disks'
SELECT Contact.FirstName, Account.NameFROM Account, Contact
SELECT Task.Subject, Contact.NameFROM Task, ContactWHERE Contact.Type='Contact'
Select Contact.Firstname, Account.NameFROM AccountJOIN ContactON Account.MyCustomColumn__c = Contact.Id
Select Contact.Firstname, Account.Name FROM Contact, Account
Select Contact.Firstname, Account.Name FROM AccountJOIN ContactON Account.MyCustomColumn__c = Contact.Id
SELECT Account.Id, Account.Name, Account.Fax, Opportunity.AccountId, Opportunity.CloseDateFROM AccountINNER JOIN OpportunityON Account.Id = Opportunity.AccountId
SELECT A.Name, B.Name, C.Name FROM NW_Product__c as
A, NW_Category__c as
B, NW_Suppliers__c as
CWHERE B.Name LIKE 'Dairy%'
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)