10 SQL Statements and Extensions for the Salesforce Driver : Select
Select
Use the Select statement to fetch results from one or more tables. The Select statement can operate on local and remote tables in any combination.
Grammar
SELECT select_clause
from_clause
[where_clause]
[groupby_clause]
[having_clause]
[{UNION [ALL | DISTINCT] |
{MINUS [DISTINCT] | EXCEPT [DISTINCT]} |
INTERSECT [DISTINCT]} select_statement]
[orderby_clause]
[limit_clause]
where:
select_clause specifies the columns from which results are to be returned by the query. See “Select Clause” for a complete explanation.
from_clause specifies one or more tables on which the other clauses in the query operate. See “From Clause” for a complete explanation.
where_clause is optional and restricts the results that are returned by the query. See “Where Clause” for a complete explanation.
groupby_clause is optional and allows query results to be aggregated in terms of groups. See “Group By Clause” for a complete explanation.
having_clause is optional and specifies conditions for groups of rows (for example, display only the departments that have salaries totaling more than $200,000). See “Having Clause” for a complete explanation.
UNION is an optional operator that combines the results of the left and right Select statements into a single result. See “Union Operator” for a complete explanation.
INTERSECT is an optional operator that returns a single result by keeping any distinct values from the results of the left and right Select statements. See “Intersect Operator” for a complete explanation.
EXCEPT and MINUS are synonymous optional operators that returns a single result by taking the results of the left Select statement and removing the results of the right Select statement. See “Except and Minus Operators” for a complete explanation.
orderby_clause is optional and sorts the results that are returned by the query. See “Order By Clause” for a complete explanation.
limit_clause is optional and places an upper bound on the number of rows returned in the result. See “Limit Clause” for a complete explanation.
Select Clause
Use the Select clause to specify with a list of column expressions that identify columns of values that you want to retrieve or an asterisk (*) to retrieve the value of all columns.
Grammar
SELECT [{LIMIT offset number | TOP number}] [ALL | DISTINCT] {* | column_expression [[AS] column_alias] [,column_expression [[AS] column_alias], ...]}
[INTO [DISK | TEMP] new_table]
where:
LIMIT offset number creates the result set for the Select statement first and then discards the first number of rows specified by offset and returns the number of remaining rows specified by number. To not discard any of the rows, specify 0 for offset, for example, LIMIT 0 number. To discard the first offset number of rows and return all the remaining rows, specify 0 for number, for example, LIMIT offset 0.
TOP number is equivalent to LIMIT 0 number.
column_expression can be simply a column name (for example, last_name). More complex expressions may include mathematical operations or string manipulation (for example, salary * 1.05). See “SQL Expressions” for details. column_expression can also include aggregate functions. See “Aggregate Functions” for details.
column_alias can be used to give the column a descriptive name. For example, to assign the alias department to the column dep:
SELECT dep AS department FROM emp
Separate multiple column expressions with commas (for example, SELECT last_name, first_name, hire_date).
Column names can be prefixed with the table name or table alias. For example, SELECT emp.last_name or e.last_name, where e is the alias for the table emp.
The DISTINCT operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example:
SELECT DISTINCT dep FROM emp
NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.
The INTO clause copies the result set into new_table. INTO DISK creates the new table in cached memory. INTO TEMP creates a temporary table.
Aggregate Functions
The result of a query can be the result of one or more aggregate functions. Aggregate functions return a single value from a set of rows. An aggregate can be used with a column name (for example, AVG(salary)) or in combination with a more complex column expression (for example, AVG(salary * 1.07)). The column expression can be preceded by the DISTINCT operator. The DISTINCT operator eliminates duplicate values from an aggregate expression.
Table 10-3 lists supported aggregate functions.
Table 10-3. Aggregate Functions 
Aggregate
Returns
AVG
The average of the values in a numeric column expression. For example, AVG(salary) returns the average of all salary column values.
COUNT
The number of values in any column expression. For example, COUNT(name) returns the number of name values. When using COUNT with a column name, COUNT returns the number of non-NULL column values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values.
MAX
The maximum value in any column expression. For example, MAX(salary) returns the maximum salary column value.
MIN
The minimum value in any column expression. For example, MIN(salary) returns the minimum salary column value.
SUM
The total of the values in a numeric column expression. For example, SUM(salary) returns the sum of all salary column values.
Except for COUNT(*), all aggregate functions exclude NULL values. The returned value type for COUNT is INTEGER and for MIN, MAX, and AVG it is the same type as the column.
Examples
Example A
In the following example, only distinct last name values are counted. The default behavior is that all duplicate values be returned, which can be made explicit with ALL.
COUNT (DISTINCT last_name)
Example B
The following example uses the COUNT, MAX, and AVG aggregate functions:
SELECT
COUNT(amount) AS numOpportunities,
MAX(amount) AS maxAmount,
AVG(amount) AS avgAmount
FROM opportunity o INNER JOIN user u
ON o.ownerId = u.id
WHERE o.isClosed = 'false' AND
u.name = 'MyName'
From Clause
The From clause indicates the tables to be used in the Select statement.
Grammar
FROM table_name [table_alias] [,...]
where:
table_name is the name of a table or a subquery. Multiple tables define an implicit inner join among those tables. Multiple table names must be separated by a comma. For example:
SELECT * FROM emp, dep
Subqueries can be used instead of table names. Subqueries must be enclosed in parentheses. See “Subquery in a From Clause” for an example.
table_alias is a name used to refer to a table in the rest of the Select statement. When you specify an alias for a table, you can prefix all column names of that table with the table alias.
Example
The following example specifies two table aliases, e for emp and d for dep:
SELECT e.name, d.deptName
FROM emp e, dep d
WHERE e.deptId = d.id
The equal sign (=) includes only matching rows in the results.
Join in a From Clause
You can use a Join as a way to associate multiple tables within a Select statement. Joins may be either explicit or implicit. For example, the following is the example from the previous section restated as an explicit inner join:
SELECT e.name, d.deptName
FROM emp e INNER JOIN dep d ON e.deptId = d.id;
Grammar
FROM table_name {RIGHT OUTER | INNER | LEFT OUTER | CROSS} JOIN table.key ON search-condition
Example
In this example, two tables are joined using LEFT OUTER JOIN. T1, the first table named includes nonmatching rows.
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.key = T2.key
If you use a CROSS JOIN, no ON expression is allowed for the join.
Subquery in a From Clause
Subqueries can be used in the From clause in place of table references (table_name). For example:
SELECT * FROM (SELECT * FROM emp WHERE sal > 10000) new_emp, dept WHERE new_emp.deptno = dept.deptno
For more information about subqueries, see “Subqueries”.
Where Clause
The Where clause specifies the conditions that rows must meet to be retrieved.
Grammar
WHERE expr1 rel_operator expr2
where:
expr1 is either a column name, literal, or expression.
expr2 is either a column name, literal, expression, or subquery. Subqueries must be enclosed in parentheses. See “Subqueries” for complete information about subqueries.
rel_operator is the relational operator that links the two expressions.
See “SQL Expressions” for details about expressions.
Example
The following Select statement retrieves the first and last names of employees that make at least $20,000.
SELECT last_name, first_name FROM emp WHERE salary >= 20000
Group By Clause
The Group By clause specifies the names of one or more columns by which the returned values are grouped. This clause is used to return a set of aggregate values.
Grammar
GROUP BY column_expression [,...]
where:
column_expression is either a column name or a SQL expression (see “SQL Expressions” for details). Multiple values must be separated by a comma. If column_expression is a column name, it must match one of the column names specified in the Select clause. Also, the Group By clause must include all non-aggregate columns specified in the Select list.
Example
The following example totals the salaries in each department:
SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id
This statement returns one row for each distinct department ID. Each row contains the department ID and the sum of the salaries of the employees in the department.
Having Clause
The Having clause specifies conditions for groups of rows (for example, display only the departments that have salaries totaling more than $200,000). This clause is valid only if you have already defined a Group By clause.
Grammar
HAVING expr1 rel_operator expr2
where:
expr1 and expr2 can be column names, constant values, or expressions. These expressions do not have to match a column expression in the Select clause. See “SQL Expressions” for details regarding SQL expressions.
rel_operator is the relational operator that links the two expressions.
Example
The following example returns only the departments that have salaries totaling more than $200,000:
SELECT dept_id, sum(salary) FROM emp
GROUP BY dept_id HAVING sum(salary) > 200000
Union Operator
The Union operator combines the results of two Select statements into a single result. The single result is all the returned rows from both Select statements. By default, duplicate rows are not returned. To return duplicate rows, use the All keyword (UNION ALL).
Grammar
select_statement
UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT]} | INTERSECT [DISTINCT]
select_statement
When using the Union operator, the Select lists for each Select statement must have the same number of column expressions with the same data types and must be specified in the same order.
Examples
Example A
The following example has the same number of column expressions, and each column expression, in order, has the same data type.
SELECT last_name, salary, hire_date FROM emp
UNION
SELECT name, pay, birth_date FROM person
Example B
The following example is not valid because the data types of the column expressions are different (salary FROM emp has a different data type than last_name FROM raises). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.
SELECT last_name, salary FROM emp
UNION
SELECT salary, last_name FROM raises
Intersect Operator
Intersect operator returns a single result set. The result set contains rows that are returned by both Select statements. Duplicates are returned unless the Distinct operator is added.
Grammar
select_statement
INTERSECT [DISTINCT]
select_statement
The Distinct operator eliminates duplicate rows from the results.
When using the Intersect operator, the Select lists for each Select statement must have the same number of column expressions with the same data types and must be specified in the same order.
Examples
Example A
The following example has the same number of column expressions, and each column expression, in order, has the same data type.
SELECT last_name, salary, hire_date FROM emp
INTERSECT [DISTINCT]
SELECT name, pay, birth_date FROM person
Example B
The following example is not valid because the data types of the column expressions are different (salary FROM emp has a different data type than last_name FROM raises). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.
SELECT last_name, salary FROM emp
INTERSECT
SELECT salary, last_name FROM raises
Except and Minus Operators
The Except and Minus are synonymous operators that return the rows from the left Select statement that are not included in the result of the right Select statement.
Grammar
select_statement
{EXCEPT [DISTINCT] | MINUS [DISTINCT]}
select_statement
The DISTINCT operator eliminates duplicate rows from the results.
When using one of these operators, the Select lists for each Select statement must have the same number of column expressions with the same data types and must be specified in the same order.
Examples
Example A
The following example has the same number of column expressions, and each column expression, in order, has the same data type.
SELECT last_name, salary, hire_date FROM emp
EXCEPT
SELECT name, pay, birth_date FROM person
Example B
The following example is not valid because the data types of the column expressions are different (salary FROM emp has a different data type than last_name FROM raises). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type.
SELECT last_name, salary FROM emp
EXCEPT
SELECT salary, last_name FROM raises
Order By Clause
The Order By clause specifies how the rows are to be sorted.
Grammar
ORDER BY sort_expression [DESC | ASC] [,...]
where:
sort_expression is either the name of a column, a column alias, a SQL expression, or the positioned number of the column or expression in the select list to use. See “SQL Expressions” for details regarding SQL expressions.
The default is to perform an ascending (ASC) sort.
Example
For example, to sort by last_name and then by first_name, you could use either of the following Select statements:
SELECT emp_id, last_name, first_name FROM emp
ORDER BY last_name, first_name
or
SELECT emp_id, last_name, first_name FROM emp
ORDER BY 2,3
In the second example, last_name is the second item in the Select list, so ORDER BY 2,3 sorts by last_name and then by first_name.
Limit Clause
The Limit clause places an upper bound on the number of rows returned in the result.
Grammar
LIMIT number_of_rows [OFFSET offset_number]
where:
number_of_rows specifies a maximum number of rows in the result. A negative number indicates no upper bound.
The OFFSET operator specifies how many rows to skip at the beginning of the result set. offset_number is the number of rows to skip.
In a compound query, the Limit clause can appear only on the final Select statement. The limit is applied to the entire query, not to the individual Select statement to which it is attached.
Example
The following example returns a maximum of 20 rows.
SELECT last_name, first_name FROM emp WHERE salary > 20000 ORDER BY dept_id LIMIT 20