Sql – Select without a FROM clause in Oracle

oraclesql

in SQL Server is possible to execute a SELECT, without reference to a table; something like:

Select 1.2 +3, 'my dummy string'

As Oracle does not allow a SELECT without a FROM, I use the dual table for this type of operation; something like:

Select 1,2+3, 'my dummy string' FROM DUAL

There is a better way of doing this type of query? it is good practice to use the dual table?

No, in Oracle there is no SELECT without FROM.

Using the dual table is a good practice.

dual is an in-memory table. If you don't select DUMMY from it, it uses a special access path (FAST DUAL) which requires no I/O.

Once upon a time, dual had two records (hence the name) and was intended to serve as a dummy recordset to duplicate records being joined with.

Now it has but one record, but you can still generate an arbitrary number of rows with it:

SELECT  level
FROM    dual
CONNECT BY
        level <= 100

MySQL also supports dual (as well as the fromless syntax).

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for: