Categories:
Query Syntax

SELECT

SELECT can be used in both a statement and a clause within a SELECT statement:

  • As a statement, the SELECT statement is the most commonly executed SQL statement; it queries the database and retrieves a set of rows.
  • As a clause, SELECT defines the set of columns returned by a query.

Syntax

[ ... ]
SELECT [ { ALL | DISTINCT } ]
    {
          [{<object_name>|<alias>}.]*
        | [{<object_name>|<alias>}.]<col_name>
        | [{<object_name>|<alias>}.]$<col_position>
        | <expr>
        [ [ AS ] <col_alias> ]
    }
    [ , ... ]
[ ... ]

For more information about SELECT as a statement, and the other clauses within the statement, see Query Syntax.

Parameters

ALL | DISTINCT

Specifies whether to perform duplicate elimination on the result set:

  • ALL includes all values in the result set.
  • DISTINCT eliminates duplicate values from the result set.

Default: ALL

*
The asterisk is shorthand to indicate that the output should include all columns of the specified object, or all columns of all objects if * is not qualified with an object name or alias.
object_name or . alias
Specifies the object identifier or object alias as defined in the FROM clause.
col_name
Specifies the column identifier as defined in the FROM clause.
$col_position
Specifies the position of the column (1-based) as defined in the FROM clause. If a column is referenced from a table, this number can’t exceed the maximum number of columns in the table.
expr
Specifies an expression, such as a mathematical expression, that evaluates to a specific value for any given row.
[ AS ] col_alias
Specifies the column alias assigned to the resulting expression. This is used as the display name in a top-level SELECT list, and the column name in an inline view.

Usage Notes

  • Aliases and identifiers are case-insensitive by default. To preserve case, enclose them within double quotes (").
  • Without an ORDER BY clause, the results returned by SELECT are an unordered set. Running the same query repeatedly against the same tables might result in a different output order every time. If order matters, use the ORDER BY clause.
  • SELECT can be used not only as an independent statement, but also as part of other statements, for example INSERT INTO ... SELECT ...;. SELECT can also be used in a subquery within a statement.

Examples

A few simple examples are provided below.

Many additional examples are included in other parts of the documentation, including the detailed descriptions of Query Syntax.

This example shows how to look up an employee’s last name if you know her ID.

SELECT last_name FROM employee_table WHERE employee_ID = 101;

This example lists each employee and the name of the department that each employee works in. The output is in order by department name, and within each department the employees are in order by name. This query uses a JOIN to relate the information in one table to the information in another table.

SELECT department_name, last_name, first_name
  FROM employee_table INNER JOIN department_table
    ON employee_table.department_ID = department_table.department_ID
  ORDER BY department_name, last_name, first_name;

This example shows that the output columns do not need to be taken directly from the tables in the FROM clause; the output columns can be general expressions. This example also shows how to use a column alias so that the output has a meaningful column name:

-- Assuming that the radius of the circle is 2.0.
SELECT pi() * 2.0 * 2.0 AS area_of_circle;
-- or
SELECT pi() * POW(2.0, 2) AS area_of_circle;