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 employee;
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 emp_name, emp_no FROM employee;
To specify the table from which the column is to be selected, use the table.column_name syntax. For example:
SELECT managers.name, employee.name
FROM manager, employee...
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 manager table, and the second column contains the values from the name column of the employee table. If a column name is used in more than one of the source tables, 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 can only be used in the first of a series of UNIONed selects. However, it can be used in the CREATE TABLE...AS SELECT and INSERT INTO...SELECT statements.
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:', emp_name, CURRENT_DATE,
IFNULL(emp_name,'Unassigned')
FROM employee;
The preceding query selects all rows from the employee table; the result table is composed of the string constant 'Name:', the name of the employee, today's date, 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 employee table):
The SELECT clause can be used to obtain values calculated from the contents of a table. For example, the following query calculates the weekly salary of each employee based on their annual salary:
SELECT emp_name, salary/52 FROM employee_dim;
Aggregate Functions can be used to calculate values based on the contents of a column. For example, the following query returns the highest, lowest, and average salary from the employee_dim table:
SELECT MAX(salary), MIN(salary), AVG(salary)
FROM employee_dim;
These values are based on the amounts stored in the salary column.
The SELECT clause can contain any
SQL function (see
SQL Functions). Especially useful in analytical processing are the windowing aggregate functions and the
analytical functions (see
Analytical Functions).
To specify a name for a column in the result table, use the AS result_column clause. In the following example, the name, weekly_salary, is assigned to the second result column:
SELECT emp_name, salary/52 AS weekly_salary
FROM employee_dim;
If a result column name is omitted 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.
More information