The SELECT statement is a form of query.
Invocation
This statement can be used in an application program using the CLI functions or issued through the CLP.
Syntax
.-,--------------.
V |
>>-SELECT--+----------+----| selectItem |-+--FROM--------------->
'-DISTINCT-'
.-,----------------------------------------.
V |
>----table-name--+--------------------------+-+----------------->
'-+----+--correlation-name-'
'-AS-'
>--+-----------------------------+------------------------------>
'-WHERE--| search_condition |-'
>--+----------------------------------------------+------------->
| .-,------------------------------. |
| V | |
'-GROUP BY----+---------------+--column-name-+-'
'-table-name--.-'
>--+----------------------------------------------------------------+-->
| .-,--------------. |
| V | |
'-ORDER BY--+---simple-integer-+-------------------------------+-'
| .-,--------------------------------------------. |
| V | |
'---+---------------+--column-name--+----------+-+-'
'-table-name--.-' '-+-ASC--+-'
'-DESC-'
>--+-----------------------+-----------------------------------><
'-LIMIT--simple-integer-'
selectItem:
|--+-*----------------------------------------+-----------------|
+-COUNT--(--+-| expression |-+--)----------+
| '-*--------------' |
+-AVG--(----| expression |----)------------+
+-SUM--(----| expression |----)------------+
+-MIN--(----| expression |----)------------+
+-MAX--(----| expression |----)------------+
+-MOD--(-| expression |-,-| expression |-)-+
+-LENGTH--(--| expression |--)-------------+
+-RTRIM--(--| expression |--)--------------+
+-LCASE--(--| expression |--)--------------+
'-UCASE--(--| expression |--)--------------'
search_condition:
.------------------------------------------------.
V |
|----+--------------------------------------------+-+-----------|
'-+-AND-+--+-----+--+-| predicate |--------+-'
'-OR--' '-NOT-' '-| search_condition |-'
predicate:
(1)
|--+-| basic predicate |-+--------------------------------------|
+-| IN predicate |----+
+-| LIKE predicate |--+
'-| NULL predicate |--'
basic predicate:
|--| expression |--+-=---+--| expression |----------------------|
+- <>-+
+- <--+
+->---+
+- <=-+
+- >=-+
'-||--'
IN predicate:
.-,--------------.
V |
|--| expression |--+-----+--IN----| expression |-+--------------|
'-NOT-'
LIKE predicate:
|--| expression |--+-----+--LIKE--| expression |----------------|
'-NOT-'
NULL predicate:
|--| expression |--IS--+-----+--NULL----------------------------|
'-NOT-'
expression:
.-operator-----------------------------------.
V |
|----+----+--+-literal------------------------+-+---------------|
+- +-+ +-+---------------+--column-name-+
'- --' | '-table-name--.-' |
+-special register---------------+
'-function-----------------------'
operator:
|--+- /-+-------------------------------------------------------|
+- *-+
+- +-+
'- --'
Anmerkungen:
Description
Arithmetic operations on CHAR, VARCHAR, BLOB(n), DATE, TIME, and TIMESTAMP data types are not supported.
The first and second arguments can be either SMALLINT or INTEGER.
The result of the function is SMALLINT if both arguments are SMALLINT; otherwise, it is an INTEGER. The result can be null; if any argument is null, the result is the null value.
The result of the function is a string. Its length is sum of the lengths of the two arguments. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The LENGTH(expression) function returns the length of a value.
The argument can be an expression that returns a value of the following built-in data types:
The result of the function is an integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the length of the argument. The length of a varying-length string is the actual length, not the maximum length.
The length of a BLOB is the number of bytes used to represent the value.
Consider a VARCHAR(50) column named ADDRESS with a value of '895 Don Mills Road'. LENGTH(ADDRESS) returns the value 18.
The RTRIM(expression) function removes blanks from the end of the string.
The argument can be a CHAR or VARCHAR data type.
The result data type of the function is always VARCHAR.
The length parameter of the returned type is the same as the length parameter of the argument data type.
The actual length of the result for character strings is the length of the string-expression minus the number of bytes removed for blank characters. The actual length of the result for graphic strings is the length (in number of double byte characters) of string-expression minus the number of double byte blank characters removed. If all of the characters are removed, the result is an empty, varying-length string (length is zero).
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Consider a CHAR(50) column named NAME with a value of 'Cliff '. RTRIM(NAME) returns 'Cliff'. LENGTH(RTRIM(NAME)) returns 5.
The LCASE or LOWER function returns a string in which all the SBCS characters have been converted to lowercase characters. That is, the characters A-Z will be translated to the characters a-z, and characters with diacritical marks will be translated to their lowercase equivalents if they exist.
The argument must be an expression whose value is a CHAR or VARCHAR data type.
The result of the function has the same data type and length attribute as the argument. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Ensure that the characters in the value of column JOB in the EMPLOYEE table are returned in lowercase characters. For example:
SELECT LCASE(JOB)
FROM EMPLOYEE
WHERE EMPNO = '000020';
If one table-reference is specified, the intermediate result table is simply the result of that table-reference. If more than one table-reference is specified, the intermediate result table consists of all possible combinations of the rows of the specified table-references (the Cartesian product). Each row of the result is a row from the first table-reference concatenated with a row from the second table-reference, concatenated in turn with a row from the third, and so on. The number of rows in the result is the product of the number of rows in all the individual table-references. A maximum of 20 tables can be specified in the FROM clause.
The result of a search_condition is derived by applying the specified logical operators (AND, OR, NOT) to the result of each specified predicate. A predicate compares two values. If logical operators are not specified, the result of the search condition is the result of the specified predicate.
Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, NOT is applied before AND, and AND is applied before OR. The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions.
The search_condition is applied to each row of the table, and the selected rows are those for which the result of the search_condition is true.
Each column-name in the search condition must identify a column of the table.
Arithmetic operations on CHAR, VARCHAR, BLOB(n), DATE, TIME and TIMESTAMP data types are not supported.
Examples:
SELECT lname, fname FROM emp WHERE state IN ('CA', 'AZ', 'OR');
SELECT c1 FROM t1 WHERE c1*5-6 IN (mod(c2,2)+5,c3+4/2);
Examples:
SELECT empid FROM emp WHERE city NOT IN ('San Jose', 'Morgan Hill', 'Santa Clara');
Rules
BLOB data type columns cannot be used in GROUP BY, ORDER BY, and DISTINCT clauses.
Notes
SELECT EMPNO, FIRSTNAME FROM EMPLOYEE ORDER BY LASTNAMEThe following query is valid:
SELECT LASTNAME, EMPNO, FIRSTNAME FROM EMPLOYEE ORDER BY LASTNAME
Examples
Example 1: Select the employees (EMPNO and LASTNAME) from the EMPLOYEE table who were hired after 01/01/1980 and put them in order of their last name (LASTNAME).
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE HIREDATE > '01/01/1980' ORDER BY LASTNAME
Example 2: Compute the average salary for each department in the EMPLOYEE table.
SELECT DEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPT
Example 3: Compute the maximum sales volume for each sales region, and display the results by region, in order of highest to lowest sales volume.
SELECT REGION, MAX(SALES_VOL) FROM SALES GROUP BY REGION ORDER BY 2 DESC
Zugehörige Referenzen