选择计数(*)； [英] SELECT COUNT(*) ;

问题描述

我有一个数据库，database1，里面有两个表(Table 1，Table2).

I have a database, database1, with two tables (Table 1, Table2) in it.

Table1 中有 3 行，Table2 中有 2 行.现在，如果我在 database1 上执行以下 SQL 查询 SELECT COUNT(*);，则输出为 "1".

There are 3 rows in Table1 and 2 rows in Table2. Now if I execute the following SQL query SELECT COUNT(*); on database1, then the output is "1".

有人知道这个"1"是什么意思吗?

Does anyone has the idea, what this "1" signifies?

两个表的定义如下.

CREATE TABLE Table1
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)

CREATE TABLE Table2
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)

推荐答案

与此类似，以下内容也返回结果.

Along similar lines the following also returns a result.

SELECT 'test'
WHERE  EXISTS (SELECT *)  

该行为的解释(来自 此连接项)也适用于您的问题.

The explanation for that behavior (from this Connect item) also applies to your question.

在 ANSI SQL 中，不允许使用没有 FROM 子句的 SELECT 语句 -您需要指定表源.所以语句 "SELECT 'test'WHERE EXISTS(SELECT *)" 应该给出语法错误.这是正确的行为.

In ANSI SQL, a SELECT statement without FROM clause is not permitted - you need to specify a table source. So the statement "SELECT 'test' WHERE EXISTS(SELECT *)" should give syntax error. This is the correct behavior.

关于 SQL Server 实现，FROM子句是可选的，它一直以这种方式工作.所以你可以做"SELECT 1" 或 "SELECT @v" 等等，不需要表格.在在其他数据库系统中，有一个名为DUAL"的虚拟表 ，其中有一个row 用于执行诸如SELECT 1 FROM"之类的 SELECT 语句dual;" 或 "SELECT @v FROM dual;".现在，来到 EXISTS 子句 -项目列表在语法或结果方面无关紧要查询和 SELECT * 在子查询中是有效的.将此与事实上，我们允许 SELECT 而没有 FROM，你会得到你想要的行为看.我们可以修复它，但这样做并没有多大价值可能会破坏现有的应用程序代码.

With respect to the SQL Server implementation, the FROM clause is optional and it has always worked this way. So you can do "SELECT 1" or "SELECT @v" and so on without requiring a table. In other database systems, there is a dummy table called "DUAL" with one row that is used to do such SELECT statements like "SELECT 1 FROM dual;" or "SELECT @v FROM dual;". Now, coming to the EXISTS clause - the project list doesn't matter in terms of the syntax or result of the query and SELECT * is valid in a sub-query. Couple this with the fact that we allow SELECT without FROM, you get the behavior that you see. We could fix it but there is not much value in doing it and it might break existing application code.

这篇关于选择计数(*)；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！