SELECT Statement

Lists rows from a table.

Syntax

SELECT { [ ALL || DISTINCT ] } select-list FROM table-reference-list{ WHERE search-condition }{ GROUP BY column-name {,column-name{...}}}{ HAVING search-condition }{ ORDER BY sort-specification {, sort-specification{...}}}

Syntax Elements

select-list =
* || select-sublist {,select-sublist{...}}

select-sublist =
expression {{AS} column-alias} || [table-name || correlation-name].*

expression =
Refer to the topic SQL Expression.

column-alias =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

table-name =
{owner-name.}table-identifier

owner-name =
The SQL user-id of the creator of the table

table-identifier =
Any user-defined name (as per column-alias above).

correlation-name =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

table-reference-list =
table-reference {,table-reference{...}}

table-reference =
table-name {correlation-name} || outer-join-extension

outer-join extension =
--(*VENDOR(Microsoft), PRODUCT (ODBC) oj outer-join*)-- || {oj outer-join}

outer-join =
table-reference LEFT OUTER JOIN {table-reference || outer-join}
ON search-condition

search-condition =
Refer to the topic SQL Search Condition.

sort-specification =
[unsigned-integer || column-name] {ASC || DESC}

column-name =
{[table-name || correlation-name].}column-identifier

column-identifier
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

Restrictions

The user performing this statement requires SQL SELECT privileges on all tables referenced.

Comment

If an ORDER BY clause is specified, this determines the sort order of the output. The result can be sorted by columns identified by name or by their ordinal position in the select-list. Each column can be sorted in ascending (ASC) or descending order (DESC).

Examples

SELECT * FROM EMP ORDER BY DEPTNO 

SELECT DEPTNO, COUNT(*) "Employees", AVG(SAL) "Avg Salary" FROM EMP GROUP BY DEPTNO 

Outer Join Examples

SELECT EMPLOYEE.NAME, DEPT.DEPTNAME
FROM --(*vendor(MICROSOFT),product(ODBC) oj
   EMPLOYEE LEFT OUTER JOIN DEPT ON EMPLOYEE.DEPTID=DEPT.DEPTID*)--
WHERE EMPLOYEE.PROJID=544

SELECT EMPLOYEE.NAME, DEPT.DEPTNAME
FROM {oj EMPLOYEE LEFT OUTER JOIN DEPT 
     ON EMPLOYEE.DEPTID=DEPT.DEPTID}
WHERE EMPLOYEE.PROJID=544