The Select clause can be used to specify column expressions that identify columns of values that you want to retrieve or an asterisk (*) to retrieve the value of all columns.

Syntax

SELECT [{LIMIT offsetnumber | TOP number}] [ALL | DISTINCT] {* | column_expression [[AS] column_alias] [,column_expression [[AS] column_alias], ...]}
[INTO [DISK | TEMP] new_table]
SELECT [{LIMIT offsetlimit | TOP limit}][ALL | DISTINCT]
{select_expression | table.* | *} [, ...]
[INTO [DISK | TEMP] new_table]

where:

LIMIT offset number creates the result set for the Select statement first and then discards the first number of rows specified by offset and returns the number of remaining rows specified by number. To not discard any of the rows, specify 0 for offset, for example, LIMIT 0 number. To discard the first offset number of rows and return all the remaining rows, specify 0 for number, for example, LIMIT offset0.

TOP number is equivalent to LIMIT 0number.

column_expression can be simply a column name (for example, last_name). More complex expressions may include mathematical operations or string manipulation (for example, salary * 1.05). See SQL expressions for details. column_expression can also include aggregate functions. See Aggregate functions for details.

column_alias can be used to give the column a descriptive name. For example, to assign the alias department to the column dep:

SELECT dep AS department FROM emp

Separate multiple column expressions with commas (for example, SELECT last_name, first_name, hire_date).

Column names can be prefixed with the table name or table alias. For example, SELECT emp.last_name or e.last_name, where e is the alias for the table emp.

The DISTINCT operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example:

SELECT DISTINCT dep FROM emp

NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.

The INTO clause copies the result set into new_table. INTO DISK creates the new table in cached memory. INTO TEMP creates a temporary table.

Notes

  • Separate multiple column expressions with commas (for example, SELECT last_name, first_name, hire_date).
  • Column names can be prefixed with the table name or table alias. For example, SELECT emp.last_name or e.last_name, where e is the alias for the table emp.
  • NULL values are not treated as distinct from each other. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.