Selects rows from one or more tables.

Syntax

---

SELECT [ALL|DISTINCT]
*|<SelectColumn> [,<SelectColumn>]
[INTO <OutputParameter>[,<OutputParameter>]]
[FROM <SelectTable> [<Join> [,<Join>]|,<SelectTable>]]
[WHERE <FilterCondition>]
[GROUP BY <GroupColumn> [,<GroupColumn>]]
[HAVING <GroupFilterCondition>]
[UNION|
UNION ALL|
INTERSECT|
INTERSECT ALL|
EXCEPT|
EXCEPT ALL <QueryExpression>]
[ORDER BY <OrderColumn> [,<OrderColumn>]]
[RANGE <Start> [TO <End>]]
[NOJOINOPTIMIZE]
[JOINOPTIMIZECOSTS]
[JOININDEXTHRESHHOLD <ThreshholdPercent>]

<SelectColumn> = <ColumnExpression> [AS <ColumnCorrelationName>]

<SelectTable> = <TableName>|<ViewName>|<DerivedTable> [AS <TableCorrelationName>]

<DerivedTable> =

(<SELECT Statement>)

<Join> = [INNER|[LEFT|RIGHT OUTER] JOIN <SelectTable> ON <JoinCondition>

<GroupColumn> = <ColumnExpression> [COLLATE <CollationName>]

<OrderColumn> = <ColumnExpression> [COLLATE <CollationName>]
[[ASC|ASCENDING]|[DESC|DESCENDING]]

<Start> = INTEGER
<End> = INTEGER

<ThreshholdPercent> = INTEGER

Usage

---

Use this statement to select rows from one or more tables in an ElevateDB database. The SELECT statement generates a result set that will contain the selected rows in the using the grouping specified by the GROUP BY statement, if present, and the ordering specified by the ORDER BY clause, if present.

ALL and DISTINCT Clauses

The ALL and DISTINCT clauses control whether the generated result set contains duplicate rows. The DISTINCT clause prevents duplicate rows while the ALL clause outputs all rows, including duplicates. The ALL clause is the default condition and does not need to be specified in order to allow for duplicate rows in the result set.

SELECT Columns

The SELECT column list specifies the list of columns to be output into the result set. The columns specified in the SELECT column list can contain any combination of columns and valid SQL expressions. The only requirement is that any column references be valid in the context of the tables being selected from via the FROM clause (see below) or via any sub-queries that are present as part of a valid SQL expression.

The special columns wildcard * can be used to specify that all columns from the first table in the FROM clause, or a specific table if prefaced with the table name using the <TableName>.* notation, be output into the result set.

Use the AS clause to output any SELECT column in the result set using a specific column name.

Expression

for general SQL expressions and a column name of:

<AggregateFunction> OF <ColumnName>|ALL

for any aggregate function expressions using the MIN, MAX, SUM, RUNSUM, AVG, STDDEV, or COUNT functions.

INTO Clause

The INTO clause allows you to specify one or more output parameters as the target of a SELECT statement. Such a statement doesn't return a result set at all, which is useful for situations where you only want one, or a few, values from a specific row in a table.


FROM Clause

The FROM clause specifies the table or view, or tables or views, from which the rows in the result set should be selected. In addition, ElevateDB supports the use of derived tables in the FROM clause. A derived table is another SELECT statement enclosed in parentheses, and can be any valid SELECT statement. ElevateDB uses temporary views to implement derived tables.

Each table or view can be assigned a correlation name that is used instead of the actual table or view name in column references. This is useful when you must specify the same table or view name more than once in the FROM clause, such as is the case when joining a table or view to itself. Each table or view's name or correlation name must be unique in the context of the FROM clause. Derived tables, however, must be assigned a correlation name so that the derived table can be indentified elsewhere in the SELECT statement.


If more than one table is specified, then the JOIN clause can be used to specify the relationship(s) between the tables. ElevateDB supports three different JOIN clauses:



WHERE Clause

The WHERE clause is used to filter the rows output into the result set after the rows have been filtered using any join expressions that may be present. The WHERE clause can contain any valid boolean SQL expression.


GROUP BY Clause

The GROUP BY clause is used to group the rows output into the result set by one or more SQL columns or expressions. Each GROUP BY column or expression may optionally include a COLLATE clause that specifies the collation that should be used for the grouping.


HAVING Clause

The HAVING clause is used to filter any rows after they have been grouped using the GROUP BY clause, but before they are output to the result set. The HAVING clause can contain any valid boolean SQL expression. Also, aggregate functions are allowed to be used in the HAVING clause.

UNION, INTERSECT, and EXCEPT Clauses

The UNION, INTERSECT, and EXCEPT clauses are used to perform set operations between two query expressions. The SELECT column list of the query expressions involved in a set operation must contain the same number of columns or expressions, and the columns or expressions must be type-compatible. The set operations work as follows:


By default, non-distinct rows are aggregated into single rows in a UNION, INTERSECT, or EXCEPT operation. Use the ALL clause to retain non-distinct rows.

ORDER BY Clause

The ORDER BY clause is used to order the rows output into the result set by one or more SQL columns or expressions. Each ORDER BY column or expression may optionally include a COLLATE clause that specifies the collation that should be used for the ordering and an ASCENDING or DESCENDING clause that specifies the direction in which the ordering should be performed. The default direction is ASCENDING.

RANGE Clause

The RANGE clause is used to limit the rows generated in the result set to the sequential range specified, with the start value being the first row to return and the end value being the last. The TO clause and end value are optional, and can be left off if you wish to return all of the rows in the result set starting with the specified first row.

You may use dynamic parameter markers instead of constant values in the RANGE clause. This permits you to prepare the query once, and then execute it multiple times with different ranges in the result set without forcing ElevateDB to re-compile the query. See your product-specific manual for more information on preparing and executing parameterized queries.

Incremental Result Set Population

The RANGE clause can also be used with insensitive result sets. ElevateDB can use the ending row value for the range to perform incremental population of the result set, resulting in better performance when you only want to see a small set of rows at a time. Combined with dynamic parameters for the start and end values, this allows you to incrementally populate the result set as each set of rows is viewed. For example, consider the following SQL, set in the client application to return an insensitive result set:

SELECT * FROM Orders
RANGE ? TO ?

Once this query is prepared, you may then execute the query many times with different values. In this example, let's assume that the first execution uses 1 as the starting parameter value and 20 for the ending parameter value. This will cause ElevateDB to populate the first 20 rows in the result set, and return these rows as the insensitive result set. The second execution uses 21 as the starting parameter value and 40 as the ending parameter value. This will cause ElevateDB to populate the next 20 rows in the result set, and return rows 21 through 40 as the insensitive result set. The third execution uses the starting and ending values of 1 and 20 again. In this case, ElevateDB won't populate any more rows into the result and will quickly return the first 20 rows again as the result set.

Please see the Result Set Cursor Sensitivity topic for more information on sensitive and insensitive result sets.

Scalar Queries

Scalar queries are SELECT statements that result in a single row containing exactly one column. Such queries can be used almost anywhere that a normal scalar value would be used. However, it is important to note that if such a query returns more than a single row, or more than one column in the single row, then an exception will be raised.

NOJOINOPTIMIZE Clause

The NOJOINOPTIMIZE clause is used to force the query optimizer to stop re-ordering joins for a SELECT statement. In certain rare cases the query optimizer might not have enough information to know that re-ordering the joins will result in worse performance than if the joins were left in their original order, so in such cases you can include this clause to force the query optimizer to not perform the join re-ordering.


JOINOPTIMIZECOSTS Clause

The JOINOPTIMIZECOSTS clause is used to force the query optimizer to use I/O cost projections to determine the most efficient way to process a join expression. If you have a join expression with multiple conditions in it, then using this clause may help improve the performance of the join expression, especially if it is already executing very slowly.

JOININDEXTHRESHHOLD Clause

As of ElevateDB 2.26, there is a new JOININDEXTHRESHHOLD keyword available for the SELECT statement. This keyword controls how ElevateDB handles optimized (indexed) WHERE conditions on tables that are the target of INNER JOINs. For more general information, please see the How ElevateDB Selects the Rows section in the Optimizer topic.

Previously, ElevateDB would simply use any available, usable index and build a bitmap that represented the set of rows, irrespective of how many rows were selected. This works fine when there are no joins, but can be problematic when the number of rows selected is large and the table is also the target of an INNER JOIN. In such cases, the INNER JOIN condition's bitmap must constantly be assigned/ANDed with the WHERE condition's bitmap and, because the join condition's bitmap typically represents a much smaller set of rows than the WHERE condition, this process of reconciling the bitmaps becomes computationally expensive and a drag on performance.

The value provided with the JOININDEXTHRESHHOLD clause is an integer value representing a percentage of rows that, when exceeded, causes ElevateDB to treat such WHERE conditions as un-optimized row scans instead of index scans. This eliminates the computationally expensive bitmap operations and drastically improves the performance of the SELECT statement. The default value for the JOININDEXTHRESHHOLD is 75. This means that a WHERE condition must select at least 75% of the rows in a table is also the target of an INNER JOIN condition in order to be converted into a row scan.

Examples

---

-- This SELECT statement selects several columns
-- from the OrderItems table along with an expression
-- for computing the extended price of an ordered item

SELECT OrderNo,
LineNo,
ItemNo,
QtyOrdered,
UnitPrice,
(QtyOrdered * UnitPrice) AS ExtendedPrice
FROM OrderItems

-- This SELECT statement selects all columns
-- from the Orders and OrderItems tables
-- joined on the OrderNo column.  Note that
-- this statement will not output any rows
-- into the result set for any rows in the
-- Orders table that do not have a corresponding
-- row in the OrderItems table

SELECT Orders.*,
OrderItems.*
FROM Orders INNER JOIN OrderItems ON
Orders.OrderNo = OrderItems.OrderNo

-- This SELECT statement solves the previous
-- issue with missing OrderItems rows by using
-- a LEFT OUTER JOIN instead.  If a corresponding
-- row does not exist in the OrderItems table
-- for a given Orders row, then the Orders row
-- will still be included and NULL values will
-- be output for all OrderItems columns

SELECT Orders.*,
OrderItems.*
FROM Orders LEFT OUTER JOIN OrderItems ON
Orders.OrderNo = OrderItems.OrderNo

-- This SELECT statement outputs all rows from
-- the Customers table where the customer has
-- not placed an order within the last year

SELECT *
FROM Customers
WHERE NOT EXISTS
  (SELECT *
   FROM Orders
   WHERE CustNo=Customers.CustNo AND
   OrderDate BETWEEN (CURRENT_DATE - INTERVAL '1' YEAR) AND CURRENT_DATE)

-- This SELECT statement outputs all customers
-- and their total orders for the last year in
-- descending order by the TotalOrdersAmount
-- SELECT column expression

SELECT Customer.CustNo,
Customer.Name,
COUNT(Orders.*) AS TotalOrders,
SUM(OrderItems.QtyOrdered * OrderItems.UnitPrice) AS TotalOrdersAmount
FROM Customers INNER JOIN Orders ON
Customer.CustNo = Orders.CustNo
INNER JOIN OrderItems ON
Orders.OrderNo = OrderItems.OrderNo
WHERE Orders.OrderDate BETWEEN (CURRENT_DATE - INTERVAL '1' YEAR) AND CURRENT_DATE)
GROUP BY Customer.CustNo, Customer.Name
ORDER BY TotalOrdersAmount DESC

-- This SELECT statement selects the total orders
-- from the Orders table for all rows where the
-- OrderDate is in January and uses the UNION
-- clause to append the total orders from the Orders
-- table where the OrderDate is in February

SELECT 'January' AS OrderMonth,
SUM(OrderItems.QtyOrdered * OrderItems.UnitPrice) AS TotalOrdersAmount
FROM Orders INNER JOIN OrderItems ON
Orders.OrderNo = OrderItems.OrderNo
WHERE Orders.OrderDate BETWEEN DATE '2006-01-01' AND DATE '2006-01-31'
UNION ALL
SELECT 'February' AS OrderMonth,
SUM(OrderItems.QtyOrdered * OrderItems.UnitPrice) AS TotalOrdersAmount
FROM Orders INNER JOIN OrderItems ON
Orders.OrderNo = OrderItems.OrderNo
WHERE Orders.OrderDate BETWEEN DATE '2006-02-01' AND DATE '2006-02-28'

-- This SELECT statement returns the
-- user-defined version for a given
-- table, or NULL if the table does
-- not exist.  It uses the INTO clause
-- to put the resultant value into an
-- output parameter.

SELECT Version INTO :Version
FROM Information.Tables WHERE Name=:Name

Required Privileges

---

The current user must be granted the SELECT privilege on all tables referenced in the FROM clause in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: