SQL 从无中选择？

SELECT from nothing?

提问by Ritwik Bose

Is it possible to have a statement like

是否有可能有这样的声明

SELECT "Hello world"
WHERE 1 = 1

in SQL?

在 SQL 中？

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

我想知道的主要事情是我可以从无中选择，即没有 FROM 子句。

回答by OMG Ponies

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

它在供应商之间并不一致——Oracle、MySQL 和 DB2 支持双重：

SELECT 'Hello world'
  FROM DUAL

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

...而 SQL Server、PostgreSQL 和 SQLite 不需要FROM DUAL：

SELECT 'Hello world'

MySQL does support both ways.

MySQL 确实支持这两种方式。

回答by rebelliard

In Oracle:

在甲骨文：

SELECT 'Hello world' FROM dual

Dual equivalent in SQL Server:

SQL Server 中的双重等效项：

SELECT 'Hello world' 

回答by chuongtv

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

更多细节在这里：http: //modern-sql.com/use-case/select-without-from

回答by RollTide

In SQL Server type:

在 SQL Server 中键入：

Select 'Your Text'

There is no need for the FROMor WHEREclause.

不需要FROMorWHERE子句。

回答by palswim

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

你可以。我在StackExchange Data Explorer 查询中使用以下几行：

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).

数据交换使用 Transact-SQL（SQL Server 对 SQL 的专有扩展）。

You can try it yourself by running a query like:

您可以通过运行如下查询自行尝试：

SELECT 'Hello world'

回答by Vadzim

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

以下是来自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.

在许多其他 RDBMS 中，不需要虚拟表，因为您可以发出如下语句：

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

这些是 RDBMS，上面通常是可能的：

• H2
• MySQL
• 安格尔
• Postgres
• SQLite
• 数据库服务器
• Sybase ASE

在其他 RDBMS 中，需要虚拟表，就像在 Oracle 中一样。因此，您需要编写以下内容：

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

这些是 RDBMS 及其各自的虚拟表：

• DB2：SYSIBM.DUAL
• 德比：SYSIBM.SYSDUMMY1
• H2：可选地支持 DUAL
• HSQLDB：INFORMATION_SCHEMA.SYSTEM_USERS
• MySQL：可选地支持 DUAL
• 甲骨文：双
• Sybase SQL Anywhere：SYS.DUMMY

Ingres 没有 DUAL，但实际上需要它，因为在 Ingres 中你不能有没有 FROM 子句的 WHERE、GROUP BY 或 HAVING 子句。

回答by simPod

For ClickHouse, the nothing is system.one

对于 ClickHouse，什么都不是 system.one

SELECT 1 FROM system.one

回答by Lukasz Szozda

There is another possibility - standalone VALUES():

还有另一种可能性 - 独立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

DBFiddle 演示

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

SQLite/PostgreSQL/DB LUW/MariaDB 10.3 支持此语法。

回答by onedaywhen

In Standard SQL, no. A WHEREclause implies a table expression.

在标准 SQL 中，没有。甲WHERE子句意味着表表达式。

From the SQL-92 spec:

来自 SQL-92 规范：

7.6 "where clause"

Function

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

7.6 “where子句”

功能

指定通过对前面的“from 子句”的结果应用“搜索条件”而导出的表。

In turn:

反过来：

7.4 "from clause"

Function

Specify a table derived from one or more named tables.

7.4 “从条款”

功能

指定从一个或多个命名表派生的表。

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

这样做的标准方法（即应该适用于任何 SQL 产品）：

SELECT DISTINCT 'Hello world' AS new_value
  FROM AnyTableWithOneOrMoreRows
 WHERE 1 = 1;

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

...假设您想将WHERE子句更改为更有意义的内容，否则可以省略。

回答by Andries

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

我认为这是不可能的。理论上： select 执行两种操作：

• 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.

第一个可以被视为水平递减，与 where-clause 相对， where-clause 子句可以被视为垂直递减。另一方面，连接可以水平扩充集合，而联合可以垂直扩充集合。

               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.

第二个是映射。一个映射，更像是一个转换器。在 SQL 中，它需要一些字段并返回零个或多个字段。在选择中，您可以使用一些聚合函数，如 sum、avg 等。或者将所有列值转换为字符串。在 C# linq 中，我们说 select 接受类型为 T 的对象并返回类型为 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 'howdy' from <table_name>. 这个特性就是mapping、select的转换器部分。你不是在打印东西，而是在转换！在你的例子中：

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.

您正在将空/空转换为，"Hello world"并将空/无表的集合缩小为一行，恕我直言，这完全没有意义。

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".

您可能会注意到，如果您不限制列数，"Hello world"则会为表中的每个可用行打印。我希望，你现在明白为什么了。您的选择不从可用列中提取任何内容，并使用文本创建一列："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.

所以，我的答案是否定的。您不能只是省略 from 子句，因为选择总是需要表列来执行。