About the Optional FROM Clause

TimesTen lets you omit the FROM clause from your SQL queries. When you omit the FROM clause, TimesTen uses the SYS.DUAL system table by default.

The SYS.DUAL system table contains a single row and a single DUMMY column whose value is X. For queries that SELECT from SYS.DUAL or for queries that do not include the FROM clause, TimesTen ensures that exactly one row of output is returned.

Consider the following query:

Command> SELECT SYSDATE FROM SYS.DUAL;
< 2026-01-24 00:39:21 >
1 row found.

The query produces a result set that contains a single row. The returned expression is the result of calling the SYSDATE function one time. If you use any other table, the query might return zero rows, one row, or many rows depending on the contents of the table in the FROM clause. By using the FROM SYS.DUAL clause, TimesTen returns one and only one row in the result.

Let's rewrite the same query, but let's omit the FROM clause from the SELECT statement. Note that TimesTen does not return an error, and the query is successful.

Command> SELECT SYSDATE;
< 2026-01-24 00:50:21 >
1 row found.

TimesTen lets you omit the FROM clause from all queries and statements that have SELECT clauses, including INSERT...SELECT.

The extended syntax is as follows:

SELECT SelectList [FROM FromClause]

TimesTen also lets you omit the USING clause from a MERGE statement. When doing so, TimesTen adds an implied USING SYS.DUAL to the handling of the statement. For more information about the MERGE statement, see MERGE.

The following examples illustrate various uses of SQL queries with the FROM clause omitted:

This example omits the FROM clause from a SELECT statement.

Command> SELECT CURRENT_USER;
< SAMPLEUSER >
1 row found.

This example omits the FROM clause from a CREATE TABLE AS SELECT statement.

Command> CREATE TABLE t AS SELECT SYSDATE d;
1 row inserted.
Command> SELECT * FROM t;
< 2026-01-24 00:44:38 >
1 row found.

This example omits the FROM clause from an INSERT... SELECT statement.

Command> INSERT INTO t SELECT SYSDATE;
1 row inserted.
Command> SELECT * FROM t;
< 2026-01-24 00:44:38 >
< 2026-01-24 00:48:18 >
2 rows found.

This example omits the FROM clause from a CREATE VIEW statement.

Command> CREATE VIEW v AS SELECT 1 col1;

This example omits the FROM clause from both sides of a UNION clause in a SELECT statement.

Command> SELECT 1 UNION SELECT 2;
< 1 >
< 2 >
2 rows found.

This example omits the FROM clause when selecting NEXTVAL from a sequence.

Command> CREATE SEQUENCE s;
Command> SELECT S.NEXTVAL;
< 1 >
1 row found.