Some databases extend the SQL standard by having optional FROM clauses in their SELECT statements. In those databases (e.g. MariaDB, MySQL, PostgreSQL, SQLite, Sybase, SQL Server), it is possible to write this:
SELECT 1 AS x, 'a' AS y
This is quite convenient whenever a synthetic row needs to be produced, or even several such rows:
SELECT 1 AS x, 'a' AS y UNION ALL
SELECT 2 AS x, 'b' AS y
Of course, the workaround in Oracle is to use the dual table:
SELECT 1 AS x, 'a' AS y FROM dual UNION ALL
SELECT 2 AS x, 'b' AS y FROM dual
But this is really tedious. Being able to omit the FROM clause would be nice.
One side-effect / benefit of this would be that certain queries over database links would be more easy to tune. Consider the annoying difference between:
SELECT CASE WHEN EXISTS (
SELECT 1 FROM t1@dblink JOIN t2@dblink ON ... JOIN t3@dblink ON ... etc.
) THEN 1 ELSE 0 END
FROM dual
And:
SELECT CASE WHEN EXISTS (
SELECT 1 FROM t1@dblink JOIN t2@dblink ON ... JOIN t3@dblink ON ... etc.
) THEN 1 ELSE 0 END
FROM dual@dblink -- Huh!
Without the requirement of writing the FROM clause, this unnecessary and often overlooked difference would disappear and the optimiser would have a slightly easier task.
Related idea:
Marked as a duplicate of