SELECT from nothing?

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 S开发者_运维知识库ELECT from nothing, ie not have a FROM clause.

---

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.

---

Try this.

Single:

SELECT *  FROM (VALUES ('Hello world')) t1 (col1) WHERE 1 = 1

Multi:

SELECT *  FROM (VALUES ('Hello world'),('Hello world'),('Hello world')) t1 (col1) WHERE 1 = 1

more detail here : http://modern-sql.com/use-case/select-without-from

---

In Oracle:

SELECT 'Hello world' FROM dual

Dual equivalent in SQL Server:

SELECT 'Hello world' 

---

Here is the most complete list of database support of dual from https://blog.jooq.org/tag/dual-table/:

In many other RDBMS, there is no need for dummy tables, as you can issue statements like these:

SELECT 1;
SELECT 1 + 1;
SELECT SQRT(2);

These are the RDBMS, where the above is generally possible:

• H2
• MySQL
• Ingres
• Postgres
• SQLite
• SQL Server
• Sybase ASE

In other RDBMS, dummy tables are required, like in Oracle. Hence, you’ll need to write things like these:

SELECT 1       FROM DUAL;
SELECT 1 + 1   FROM DUAL;
SELECT SQRT(2) FROM DUAL;

These are the RDBMS and their respective dummy tables:

• DB2: SYSIBM.DUAL
• Derby: SYSIBM.SYSDUMMY1
• H2: Optionally supports DUAL
• HSQLDB: INFORMATION_SCHEMA.SYSTEM_USERS
• MySQL: Optionally supports DUAL
• Oracle: DUAL
• Sybase SQL Anywhere: SYS.DUMMY

Ingres has no DUAL, but would actually need it as in Ingres you cannot have a WHERE, GROUP BY or HAVING clause without a FROM clause.

---

In SQL Server type:

Select 'Your Text'

There is no need for the FROM or WHERE clause.

---

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'

---

There is another possibility - standalone VALUES():

VALUES ('Hello World');

Output:

column1
Hello World

---

It is useful when you need to specify multiple values in compact way:

VALUES (1, 'a'), (2, 'b'), (3, 'c');

Output:

column1     column2
      1     a
      2     b
      3     c

DBFiddle Demo

This syntax is supported by SQLite/PostgreSQL/DB LUW/MariaDB 10.3.

---

In Firebird, you can do this:

select "Hello world" from RDB$DATABASE;

RDB$DATABASE is a special table that always has one row.

---

I think it is not possible. Theoretically: select performs two sorts of things:

• narrow/broaden the set (set-theory);

• mapping the result.

The first one can be seen as a horizontal diminishing opposed to the where-clause which can be seen as a vertical diminishing. On the other hand, a join can augment the set horizontally where a union can augment the set vertically.

               augmentation          diminishing
horizontal     join/select              select   
vertical          union            where/inner-join

The second one is a mapping. A mapping, is more a converter. In SQL it takes some fields and returns zero or more fields. In the select, you can use some aggregate functions like, sum, avg etc. Or take all the columnvalues an convert them to string. In C# linq, we say that a select accepts an object of type T and returns an object of type U.

I think the confusion comes by the fact that you can do: select 'howdy' from <table_name>. This feature is the mapping, the converter part of the select. You are not printing something, but converting! In your example:

SELECT "
WHERE 1 = 1

you are converting nothing/null into "Hello world" and you narrow the set of nothing / no table into one row, which, imho make no sense at all.

You may notice that, if you don't constrain the number of columns, "Hello world" is printed for each available row in the table. I hope, you understand why by now. Your select takes nothing from the available columns and creates one column with the text: "Hello world".

So, my answer is NO. You can't just leave out the from-clause because the select always needs table-columns to perform on.

---

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' AS new_value
  FROM AnyTableWithOneOrMoreRows
 WHERE 1 = 1;

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

---

For ClickHouse, the nothing is system.one

SELECT 1 FROM system.one

---

I know this is an old question but the best workaround for your question is using a dummy subquery:

SELECT 'Hello World'
FROM (SELECT name='Nothing') n
WHERE 1=1

This way you can have WHERE and any clause (like Joins or Apply, etc.) after the select statement since the dummy subquery forces the use of the FROM clause without changing the result.

---

For DB2:

`VALUES('Hello world')`

You can do multiple "rows" as well:

`VALUES('Hello world'),('Goodbye world');`

You can even use them in joins as long as the types match:

VALUES(1,'Hello world')
UNION ALL
VALUES(2,'Goodbye world');

---

I'm using firebird First of all, create a one column table named "NoTable" like this

CREATE TABLE NOTABLE 
(
  NOCOLUMN              INTEGER
);
INSERT INTO NOTABLE VALUES (0); -- You can put any value

now you can write this

select 'hello world' as name

from notable

you can add any column you want to be shown