SELECT from nothing?

Question

Is it possible to have a statement like

SELECT "Hello world"
WHERE 1 = 1

in SQL?

The main thing I want to know, is can I SELECT from nothing, ie not have a FROM clause.

Answer 1

In Oracle:

SELECT 'Hello world' FROM dual

Dual equivalent in SQL Server:

SELECT 'Hello world' 

Answer 2

You can. I'm using the following lines in a StackExchange Data Explorer query:

SELECT
(SELECT COUNT(*) FROM VotesOnPosts WHERE VoteTypeName = 'UpMod' AND UserId = @UserID AND PostTypeId = 2) AS TotalUpVotes,
(SELECT COUNT(*) FROM Answers WHERE UserId = @UserID) AS TotalAnswers

The Data Exchange uses Transact-SQL (the SQL Server proprietary extensions to SQL).

You can try it yourself by running a query like:

SELECT 'Hello world'

Answer 3

It's not consistent across vendors - Oracle, MySQL, and DB2 support dual:

SELECT 'Hello world'
  FROM DUAL

...while SQL Server, PostgreSQL, and SQLite don't require the FROM DUAL:

SELECT 'Hello world'

MySQL does support both ways.

Answer 4

In Standard SQL, no. A WHERE clause implies a table expression.

From the SQL-92 spec:

7.6 "where clause"

Function

Specify a table derived by the application of a "search condition" to the result of the preceding "from clause".

In turn:

7.4 "from clause"

Function

Specify a table derived from one or more named tables.

A Standard way of doing it (i.e. should work on any SQL product):

SELECT DISTINCT "Hello world"
  FROM AnyTableWithOneOrMoreRows
 WHERE 1 = 1;

...assuming you want to change the WHERE clause to something more meaningful, otherwise it can be omitted.