Select Statement Clauses

The select statement has the following clauses:

• Select
• From
• Where
• Group by
• Having
• Order by
• Union

The following sections describe how the clauses are processed and explain each clause in detail.

Select

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 table.column_name syntax. For example:

select managers.name, employees.name
        from managers, employees...

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, qualify the column name with the table to which it belongs, or with a correlation name. For details, see From.

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. Note that this clause cannot be used in a where clause subselect and it can only be used in the first of a series of union'ed 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:', ename, date('today'),
ifnull(edept,'Unassigned')
from employees;

The preceding query selects all rows from the employees table; the result table is composed of the string constant 'Name:', the name of the employee, today's date (specified using 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 the weekly salary of each employee based on his/her 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.

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 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.