SELECT Clause
The SELECT clause specifies which values are to be returned. To display all the columns of a table, use the asterisk wildcard character (*). For example, the following query displays all rows and columns from the employees table:
select * from employees;
To select specific columns, specify the column names. For example, the following query displays all rows, but only two columns from the employees table:
select ename, enumber from employees;
To specify the table from which the column is to be selected, use the [schema.]table.columnname syntax. For example:
select personnel.managers.name,
personnel.employees.name
In the preceding example, both source tables contain a column called, name. The column names are preceded by the name of the source table. The first column of the result table contains the values from the name column of the managers table, and the second column contains the values from the name column of the employees table. If a column name is used in more than one of the source tables, you must qualify the column name with the table to which it belongs, or with a correlation name. For details, see From Clause.
The number of rows in the result table can be limited using the first clause. RowCount is a positive integer value that indicates the maximum rows in the result table. The query is effectively evaluated without concern for the first clause, but only the first “n” rows (as defined by rowCount) are returned. This clause cannot be used in a WHERE clause subselect and it may only be used in the first of a series of UNIONed selects. However, it may be used in the CREATE TABLE...AS SELECT and INSERT INTO...SELECT statements. When used with CREATE TABLE...AS SELECT and INSERT INTO…SELECT statements, first n should not be used with the ORDER BY clause.
To eliminate duplicate rows from the result table, specify the keyword DISTINCT. To preserve duplicate rows, specify the keyword all. By default, duplicate rows are preserved.
For example, the following table contains order information. The partno column contains duplicate values, because different customers have placed orders for the same part.
The following query displays the part numbers for which there are orders on file:
select distinct partno from orders
The result table looks like this:
A constant value can be included in the result table. For example:
select 'Name:', ename, date('today'),
edept from employees;
The preceding query selects all rows from the employees table. The result table is composed of the string constant 'Name:', the employee's name, today's date (specified using the constant today), and the employee's department, or if there is no department assigned, the string constant 'Unassigned'.
The result table looks like this (depending, of course, on the data in the employees table):
The SELECT clause can be used to obtain values calculated from the contents of a table. For example:
select ename, annual_salary/52 from employees;
The preceding query calculates each employee's weekly salary based on their annual salary.
Aggregate functions can be used to calculate values based on the contents of column. For example:
select max(salary), min(salary), avg(salary)
from employees;
The preceding query returns the highest, lowest, and average salary from the employees table. These values are based on the amounts stored in the salary column. For details about aggregate functions, see the chapter "Elements of OpenSQL Statements.”
To specify a name for a column in the result table, use the
AS result_column clause. For example:
select ename, annual_salary/52 as weekly_salary
from employees;
In the preceding example, the name, weekly_salary, is assigned to the second result column. If you omit a result column name for columns that are not drawn directly from a table (for example, calculated values or constants), the result columns are assigned the default name COLn, where n is the column number. Result columns are numbered from left to right. Column names cannot be assigned in SELECT clauses that use the asterisk wildcard (*) to select all the columns in a table.
Last modified date: 08/14/2024