Retrieves specified information from a database. A SELECT statement creates a temporary view.

The following remarks cover the following topics related to use of SELECT:

SELECT FOR UPDATE locks the row or rows within the table that is selected by the query. The record locks are released when the next COMMIT or ROLLBACK statement is issued.

To avoid contention, SELECT FOR UPDATE locks the rows as they are retrieved.

SELECT FOR UPDATE takes precedence within a transactions if statement level SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_LOCK. If SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_READ_ONLY, the database engine does not return an error.

SELECT FOR UPDATE does not support a WAIT or NOWAIT keyword. SELECT FOR UPDATE returns status code 84: The record or page is locked if it cannot lock the rows within a brief period (20 retries).

The SELECT FOR UPDATE statement has the following constraints:

In addition to supporting a GROUP BY on a column list, Zen supports a GROUP BY on an expression list or on any expression in a GROUP BY expression list. See GROUP BY for more information on GROUP BY extensions. HAVING is not supported without GROUP BY.

Result sets and stored views generated by executing SELECT statements with any of the following characteristics are read-only (they cannot be updated). That is, using a positioned UPDATE or a positioned DELETE and a SQLSetPos call to add, alter or delete data is not allowed on the result set or stored view if:

Dynamic parameters (?) are not supported as SELECT items. You may use dynamic parameters in any SELECT statement if the dynamic parameter is part of the predicate. For example, SELECT * FROM faculty WHERE id = ? is valid because the dynamic parameter is part of the predicate.

Note that you cannot use SQL Editor in Zen Control Center to execute a SQL statement with a dynamic parameter in the predicate.

You may use variables as SELECT items only within stored procedures. See CREATE PROCEDURE.

Aliases may appear in a WHERE, HAVING, ORDER BY, or GROUP BY clause. Alias names must differ from any column names within the table. The following statement shows the use of aliases, a and b, in a WHERE clause and in a GROUP BY clause.

When using the SUM aggregate function to sum a field that is of type DECIMAL, the following rules apply:

The precision of the result is 74, while the scale is dependent on the column definition.

The result may cause an overflow error if a number with precision greater than 74 is calculated (a very large number indeed). If an overflow occurs, no value is returned, and SQLSTATE is set to 22003, indicating a numeric value is out of range.

A subquery is a SELECT statement with one or more SELECT statements within it. A subquery produces values for further processing within the statement. The maximum number of nested subqueries allowed within the topmost SELECT statement is 16.

The following types of subqueries are supported:

Correlated subquery predicates are not supported in a HAVING clause that references grouped columns.

Expression subqueries allow the subquery within the SELECT list. For example, SELECT (SELECT SUM(c1) FROM t1 WHERE t1.c2 = t1.(c2) FROM t2. Only one item is allowed in the subquery SELECT list. For example, the following statement returns an error because the subquery SELECT list contains more than one item: SELECT p.id, (SELECT SUM(b.amount_owed), SUM(b.amount_paid) FROM billing b) FROM person p.

A subquery as an expression may be correlated or noncorrelated. A correlated subquery references one or more columns in any of the tables in the topmost statement. A noncorrelated subquery references no columns in any of the tables in the topmost statement. The following example illustrates a correlated subquery in a WHERE clause:

A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.

Both correlated and noncorrelated subqueries can return only a single value. For this reason, both correlated and noncorrelated subqueries are also referred to as scalar subqueries.

Scalar subqueries may appear in the DISTINCT, GROUP BY, and ORDER BY clause.

You may use a subquery on the left-hand side of an expression:

Expr-or-SubQuery CompareOp Expr-or-SubQuery

where Expr is an expression, and CompareOp is one of:

The rest of this section covers the following topics:

Left-hand subquery behavior has been optimized for IN, NOT IN, and =ANY in cases where the subquery is not correlated and any join condition is an outer join. Other conditions may not be optimized. Here is an example of a query that meets these conditions:

Performance improves if you use an index in the subquery because Zen optimizes a subquery based on the index. For example, the subquery in the following statement is optimized on student_id because it is an index in the Billing table:

Parentheses on different UNION groups within a subquery are not allowed. Parentheses are allowed within each SELECT statement.

For example, the parenthesis following IN and the last parenthesis are not allowed the following statement:

Table subqueries can be used to combine multiple queries into one detailed query. A table subquery is a dynamic view, which is not persisted in the database. When the topmost SELECT query finishes, all resources associated with table subqueries are released.

The following examples of pagination (1500 rows with 100 rows per page) show the use of table subqueries with the ORDER BY keyword:

The first 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 100 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name desc ) AS bar ORDER BY last_name ASC

The second 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 200 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC

…

The fifteenth 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 1500 * FROM person ORDER BY last_name ASC ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC

The table hint functionality allows you to specify which index, or indexes, to use for query optimization. A table hint overrides the default query optimizer used by the database engine.

If the table hint specifies INDEX(0), the engine performs a table scan of the associated table. (A table scan reads each row in the table rather than using an index to locate a specific data element.)

If the table hint specifies INDEX(index-name), the engine uses index-name to optimize the table based on restrictions of any JOIN conditions, or based on the use of DISTINCT, GROUP BY, or ORDER BY. If the table cannot be optimized on the specified index, the engine attempts to optimize the table based on any existing index.

If you specify multiple index names, the engine chooses the index that provides optimal performance or uses the multiple indexes for OR optimization. An example helps clarify this. Suppose that you have the following:

The database engine uses ndx1 to optimize on c1 = 1 rather than using ndx2 for optimization. Ndx3 is not considered because the table hint does not include ndx3.

Now consider the following:

The engine uses both ndx1 and ndx2 to optimize on (c1 = 1 OR c2 > 1).

The order in which the multiple index names appear in the table hint does not matter. The database engine chooses from the specified indexes the one(s) that provides for the best optimization.

Duplicate index names within the table hint are ignored.

For a joined view, specify the table hint after the appropriate table name, not at the end of the FROM clause. For example, the following statement is correct:

Contrast this with the following statement, which is incorrect:

This simple SELECT statement retrieves all the data from the Faculty table.

This statement retrieves the data from the person and the faculty table where the id column in the person table is the same as the id column in the faculty table.

The rest of this section provides examples of variations on SELECT statements. Some of these headings are based on the variable given in the syntax definition for SELECT.

The following example uses table t1 to demonstrate the use of FOR UPDATE. Assume that t1 is part of the Demodata sample database. The stored procedure creates a cursor for the SELECT FOR UPDATE statement. A loop fetches each record from t1 and, for those rows where c1=2, sets the value of c1 to four.

The procedure is called by passing the value 2 as the IN parameter.

The example assumes two users, A and B, logged in to Demodata. User A performs the following:

Notice that a WHILE loop delays the COMMIT of the transaction. During that delay, assume that User B attempts to update t1. Status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

The following example uses table t1 to demonstrate how SELECT FOR UPDATE locks records when the statement is used outside of a stored procedure. Assume that t1 is part of the Demodata sample database.

The example assumes that two users, A and B, are logged in to Demodata. User A performs the following:

(turn off AUTOCOMMIT)

(execute and fetch): "SELECT * FROM t1 WHERE c1 = 2 FOR UPDATE"

The two records where c1 = 2 are locked until User A issues a COMMIT WORK or ROLLBACK WORK statement.

(User B attempts to update t1): "UPDATE t1 SET c1=3 WHERE c1=2" A status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

(Now assume that User A commits the transaction.) The two records where c1 = 2 are unlocked.

User B could now execute "UPDATE t1 SET c1=3 WHERE c1=2" and change the values for c1.

The syntax expression1 BETWEEN expression2 and expression3 returns TRUE if expression1 >= expression2 and expression1<= expression3. FALSE is returned if expression1 >= expression3, or is expression1 <= expression2.

Expression2 and expression3 may be dynamic parameters (for example, SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?).

The next example retrieves the first names from the Person table whose ID falls between 10000 and 20000.

Both table and column correlation names are supported.

The following example selects data from both the person table and the faculty table using the aliases T1 and T2 to differentiate between the two tables.

The correlation name for a table name can also be specified in using the FROM clause, as seen in the following example:

This selects the records from table Person table where the first names are Bill and Roosevelt.

The aggregate functions for AVG (average), MAX (maximum), MIN (minimum), and SUM operate as commonly expected. The following examples use these functions with the Salary field in the Faculty sample table.

The following example retrieves student_id and sum of the amount_paid where it is greater than or equal to 100 from the billing table. It then groups the records by student_id.

If the expression is a positive integer literal, then that literal is interpreted as the number of the column in the result set and ordering is done on that column. No ordering is allowed on set functions or an expression that contains a set function.

COUNT(expression) and COUNT_BIG(expression) count all nonnull values for an expression across a predicate. COUNT(*) and COUNT_BIG(*) count all values, including NULL values. COUNT() returns an INTEGER data type with a maximum value of 2,147,483,647. COUNT_BIG() returns a BIGINT data type with a maximum value of 9,223,372,036,854,775,807.

The following example returns a count of chemistry majors who have a grade point average of 3.5 or greater (and the result does not equal null).

The following returns the standard deviation of the grade point average by major from the Student sample table.

The following returns the standard deviation for the population of the grade point average by major from the Student sample table.

The following returns the statistical variance of the grade point average by major from the Student sample table.

The following returns the statistical variance for the population of the grade point average by major from the Student sample table.

Note that for STDEV, STDEVP, VAR, and VARP, the expression must be a numeric data type and an eight-byte DOUBLE is returned. A floating-point overflow error results if the difference between the minimum and maximum values of the expression is out of range. Expression cannot contain aggregate functions. There must be at least two rows with a value in the expression field or a result is not calculated and returns a NULL.

See Date Values.

See Time Values.

See Time Stamp Values.

See String Values.

See Date Arithmetic.

Zen supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).

You may also subtract one date from another to yield a number of days.

The IF system scalar function provides conditional execution based on the truth value of a condition

This expression prints the column header as Prime1 and amount owed as 2000 where the value of the column amount_owed is 2000 or it prints a 0 if the value of the amount_owed column is not equal to 2000.

From table Class, the following example prints the value in the Section column if the section is equal to 001, else it prints “xxx” under column header Prime1.

Under column header Prime2, it prints the value in the Section column if the value of the section column is equal to 002, or else it prints “yyy.”

You can combine header Prime1 and header Prime2 by using nested IF functions. Under column header Prime, the following query prints the value of the Section column if the value of the Section column is equal to 001 or 002. Otherwise, it print “xxx.”

When needed, a database name may be prepended to an aliased table name in the FROM clause, to distinguish among tables from two or more different databases that are used in a join.

All of the specified databases must be serviced by the same database engine and have the same database code page settings. The databases do not need to reside on the same physical volume. The current database may be secure or unsecure, but all other databases in the join must be unsecure. With regard to Referential Integrity, all RI keys must exist within the same database. (See also Encoding.)

Literal database names are not permitted in the select-list or in the WHERE clause. If you wish to refer to specific columns in the select-list or in the WHERE clause, you must use an alias for each specified table. See examples.

Assume two separate databases, named accounting and customers, exist on the same server. You can join tables from the two databases using table aliases and SQL syntax similar to the following example:

In this example, the two separate databases are acctdb and loandb. The table aliases are a and b, respectively.

The following example shows how to access the Person and Student tables from the Demodata database to obtain the Last Name, First Initial of the First Name and GPA of students. With the LEFT OUTER JOIN, all rows in the Person table are fetched (the table to the left of LEFT OUTER JOIN). Since not all people have GPAs, some of the columns have NULL values for the results. This is how outer join works, returning nonmatching rows from either table.

Assume that you want to know everyone with perfectly rounded GPAs and have them all ordered by the length of their last name. Using the MOD statement and the LENGTH scalar function, you can achieve this by adding the following to the query:

The difference between LEFT and RIGHT OUTER JOIN is that all non matching rows show up for the table defined to the right of RIGHT OUTER JOIN. Change the query for LEFT OUTER JOIN to include a RIGHT OUTER JOIN instead. The difference is that the all nonmatching rows from the right table, in this case Student, show up even if no GPA is present. However, since all rows in the Student table have GPAs, all rows are fetched.

A Cartesian join is the matrix of all possible combinations of the rows from each of the tables. The number of rows in the Cartesian product equals the number of rows in the first table times the number of rows in the second table.

Assume you have the following tables in your database.

The following performs a Cartesian JOIN on these tables:

This results in the following data set:

DISTINCT is useful in aggregate functions. When used with SUM, AVG, and COUNT, it eliminates duplicate values before calculating the sum, average or count. With MIN, and MAX, however, it is allowed but does not change the result of the returned minimum or maximum.

For example, assume you want to know the salaries for different departments, including the minimum, maximum and salary, and you want to remove duplicate salaries. The following statement does this, excluding the computer science department:

On the other hand, to include duplicate salaries, drop DISTINCT:

For the use of DISTINCT in SELECT statements, see DISTINCT.

You can set the maximum number of rows returned by a SELECT statement by using the keywords TOP or LIMIT. The number must be a literal positive value. It is defined as a 32-bit unsigned integer. For example:

returns the first 10 rows of the Person table in Demodata.

LIMIT is identical to TOP except that it provides the OFFSET keyword to enable you to “scroll” through the result set by choosing the first row in the returned records. For example, if the offset is 5, then the first row returned is row 6. LIMIT has two ways to specify the offset, both with and without the OFFSET keyword, as shown in the following examples, which return identical results:

Note that when you do not use the OFFSET keyword, you must put the offset value before the row count, separated by a comma.

You can use TOP or LIMIT with ORDER BY. If so, then the database engine generates a temporary table and populates it with the entire query result set if no index can be used for ORDER BY. The rows in the temporary table are arranged as specified by ORDER BY in the result set, but only the number of rows determined by TOP or LIMIT are returned by the query.

Views that use TOP or LIMIT may be joined with other tables or views.

The main difference between TOP or LIMIT and SET ROWCOUNT is that TOP or LIMIT affect only the current statement, while SET ROWCOUNT affects all subsequent statements issued during the current database session.

If SET ROWCOUNT and TOP or LIMIT are both used in a query, the query returns a number of rows equal to the lowest of the two values.

Either TOP or LIMIT is allowed within a single query or subquery, but not both.

A SELECT query with a TOP or LIMIT clause that uses a dynamic cursor converts the cursor type to static. Forward-only and static cursors are not affected.

The following examples use both TOP and LIMIT clauses, which are interchangeable as keywords and give the same results, although LIMIT offers more control of which rows are returned.

The following examples show a variety of behaviors when TOP or LIMIT is used in views, unions, or subqueries.

The following example returns last name and amount owed for students above a certain ID number.

This topic provides working examples for table hints. Use the SQL statements to create them in Zen.

Certain restrictions apply to the use of table hints. See Table Hint Restrictions for examples.

The following example optimizes on index it1c1c2.

Contrast this with the following example, which optimizes on index it1c1 instead of on it1c2 because the restriction consists of only c1 = 1. If a query specifies an index that cannot be used to optimize the query, the hint is ignored.

The following example performs a table scan of table t1.

The following example optimizes on indexes it1c1c2 and it1c2c1.

The following example using a table hint in the creation of a view. When all records are selected from the view, the SELECT statement optimizes on index it1c1c2.

The following example uses a table hint in a subquery and optimizes on index it1c1c2.

The following example uses a table hint in a subquery and an alias name “a.” The alias name is required.

The following example optimizes the query based on the c1 = 1 restriction and optimizes the GROUP BY clause based on index it1c1c2.

The following example optimizes on index it1c1 and, unlike the previous example, optimizes only on the restriction and not on the GROUP BY clause.

Since the GROUP BY clause cannot be optimized using the specified index, it1c1, the database engine uses a temporary table to process the GROUP BY.

The following example uses a table hint in a JOIN clause and optimizes on index it1c1c2.

Contrast this with the following statement, which does not use a table hint and optimizes on index it1c1.

The following example uses a table hint in a JOIN clause to perform a table scan of table t1.

Contrast this with the following example which also performs a table scan of table t1. However, because no JOIN clause is used, the statement uses a temporary table join.