====== SELECT Statement ======

SELECT statement allows you to run queries. 

===== SELECT TOP n =====

You can specify a SELECT statement with the TOP clause. HPL/SQL automatically converts it to LIMIT clause for Hive.  

**Example**:

<code language="sql">
SELECT TOP 3 name FROM sales;    -- SELECT name FROM sales LIMIT 3 is executed
</code>

**Compatibility**: Microsoft SQL Server.

**Version**: HPL/SQL 0.3.7

===== SELECT Without FROM Clause =====

You can specify a SELECT statement without FROM. HPL/SQL can automatically add the FROM clause using the table name defined by the [[configuration#hplsqldualtable|hplsql.dual.table]] option.  

By default, HPL/SQL uses //default.dual// table name. Make sure such table exists in the database and contains exactly one row and one column only.

**Example**:

<code language="sql">
SELECT unix_timestamp();    -- SELECT unix_timestamp() FROM default.dual is executed
</code>

**Compatibility**: Microsoft SQL Server, PostgreSQL, MySQL and Netezza.

**Version**: HPL/SQL 0.3

===== FROM TABLE (VALUES ... ) Clause =====

You can generate table rows using TABLE (VALUES ...) clause (row constructor).

Syntax:

<code language="sql">
FROM TABLE (VALUES row, ...) AS table_alias (col, ...)
</code>
 
**Example 1**:

<code language="sql">
SELECT * FROM TABLE (VALUES 1, 2, 3) AS rn(rownum);

rownum
------
1
2
3
</code>

**Example 2**:

<code language="sql">
SELECT * FROM (VALUES (1,1), (2,2), (3,3)) AS rn(c1, c2);

c1     c2
---------
1      1
2      2
3      3
</code>

Note that HPL/SQL transforms FROM (VALUES ...) clause to SELECT UNION ALL subquery before the execution in Hive:

<code language=sql>
SELECT * FROM
(SELECT 1 AS c1, 1 AS c2 FROM default.dual
 UNION ALL
 SELECT 2 AS c1, 2 AS c2 FROM default.dual
 UNION ALL
 SELECT 3 AS c1, 3 AS c2 FROM default.dual) rn
</code>

You can specify the single row, single column dual table using [[configuration#hplsqldualtable|hplsql.dual.table]] option.

**Compatibility**: IBM DB2, Microsoft SQL Server

**Version**: HPL/SQL 0.3

See also:
  * [[select-into|SELECT INTO]]