SELECT

It is possible to use SELECT both as a statement and as a clause within another statement.

• The SELECT statement queries the database and retrieves a set of rows.
• As a clause, SELECT defines the set of columns returned by a query.

See also additional options under Query Syntax.

Syntax | Selecting All Columns

[ ... ]
SELECT 
    [ALL | DISTINCT]
       [[obj_name | alias].*]

Syntax | Selecting Specific Columns

[ ... ]
SELECT 
       [ALL | DISTINCT]
       {
         [[obj_name | alias].]col_name
         | 
         [[obj_name | alias].]$col_pos
         | 
         expr
       }
       [[AS] col_alias]
       [ , ... ]
[ ... ]

Parameters and Keywords

ALL | DISTINCT: Defines how duplicates are handled in the result set:

• ALL includes all values in the result set (default).
• DISTINCT removes duplicate values from the result set.

---

object_name or alias: The object identifier or object alias as defined in the FROM clause.

---

'*' (asterisk): Indicates that the output should include all columns of the specified object or all columns of all objects.

Important

If '*' already qualifies an object in this query, only that object will be returned.

---

col_name: The column identifier as defined in the FROM clause.

---

expr: An expression that results in a specific value for a particular row.

---

[AS] col_alias: The column alias assigned to the resulting expression. The column alias is used as the display name in a top-level SELECT list and as the column name in an inline view.

Tip

Use only unique column aliases. Column aliases should not be identical to the names of other columns or aliases referenced in the query.

Usage Notes

• Matching of alias names and object identifiers is case-insensitive by default.
• Case-sensitive matching can be enforced by enclosing alias names and object identifiers in double quotes '"'.
• By default, results returned by SELECT are an unordered set.
• The ORDER BY clause ensures that the order of rows in the returned results follows the defined rules if order is important.
• SELECT can also be used as a clause in other statements (e.g., INSERT INTO ... SELECT ...) or in a subquery within a statement.
• If a column alias for an expression (e.g., expr AS col_alias) is used in multiple sections of the same query such as JOIN, FROM, WHERE, GROUP BY, etc., the expression is evaluated only once in many cases, but in some cases it is evaluated multiple times, leading to different values for the alias used in different sections of the same query.

Example 1 | Selecting All Columns in the Table

SELECT * FROM (SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
               UNION ALL 
               SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
               UNION ALL
               SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2);

+------+------+------+------+
| ORD1 | GRP1 | COL1 | COL2 |
+------+------+------+------+
| a    | g1   | 10   | 10   |
| b    | g1   | 20   | 20   |
| c    | g1   | 20   | 20   |
+------+------+------+------+

Example 2 | SELECT a Column by Name

SELECT col2 FROM (  SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
                    UNION ALL 
                    SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
                    UNION ALL
                    SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 30 AS col2);

+------+
| COL2 |
+------+
| 10   |
| 20   |
| 30   |
+------+

Example 3 | SELECT and WHERE

Selecting col2 for ord1 = 'b'.

SELECT col2 FROM (  SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
                     UNION ALL 
                     SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
                     UNION ALL
                     SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 30 AS col2)
             WHERE ord1 = 'b';

+------+
| COL2 |
+------+
| 20   |
+------+

Example 4 | JOIN and Selecting Columns by Name from Multiple Tables

SELECT ord1, grp21, col3
                          FROM (      SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
                                      UNION ALL 
                                      SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
                                      UNION ALL
                                      SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 30 AS col2) t1
                          INNER JOIN 
                                   (  SELECT 'a' AS ord21, 'g21' AS grp21, '100' AS col3
                                      UNION ALL 
                                      SELECT 'b' AS ord21, 'g21' AS grp21, '120' AS col3
                                      UNION ALL
                                      SELECT 'c' AS ord21, 'g21' AS grp21, '130' AS col3) t2
                          ON       t1.ord1 = t2.ord21
                          ORDER BY ord1, col3;

+------+-------+------+
| ORD1 | GRP21 | COL3 |
+------+-------+------+
| a    | g21   | 100  |
| b    | g21   | 120  |
| c    | g21   | 130  |
+------+-------+------+

Example 5 | AS Alias for a Column

SELECT 3.1416 * 20.0 * 20.0 AS pi_z_z_a;

+----------+
| PI_Z_Z_A |
|----------|
|  1256.64 |
+----------+