SQL 任何数据库表上的“select count(1) from table_name”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/181272/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does "select count(1) from table_name" on any database tables mean?
提问by Nrj
When we execute select count(*) from table_nameit returns the number of rows.
当我们执行select count(*) from table_name它时返回行数。
What does count(1)do? What does 1signify here? Is this the same as count(*)(as it gives the same result on execution)?
有什么作用count(1)？1这里是什么意思？这与count(*)（因为它在执行时给出相同的结果）相同吗？
回答by Jeffrey L Whitledge
The parameter to the COUNT function is an expression that is to be evaluated for each row. The COUNT function returns the number of rows for which the expression evaluates to a non-null value. ( * is a special expression that is not evaluated, it simply returns the number of rows.)
COUNT 函数的参数是要为每一行计算的表达式。COUNT 函数返回表达式计算结果为非空值的行数。（ * 是一个不计算的特殊表达式，它只返回行数。）
There are two additional modifiers for the expression: ALL and DISTINCT. These determine whether duplicates are discarded. Since ALL is the default, your example is the same as count(ALL 1), which means that duplicates are retained.
表达式有两个额外的修饰符：ALL 和 DISTINCT。这些决定是否丢弃重复项。由于 ALL 是默认值，因此您的示例与 count(ALL 1) 相同，这意味着保留重复项。
Since the expression "1" evaluates to non-null for every row, and since you are not removing duplicates, COUNT(1) should always return the same number as COUNT(*).
由于表达式“1”对每一行的计算结果都为非空，并且由于您没有删除重复项，因此 COUNT(1) 应始终返回与 COUNT(*) 相同的数字。
回答by Eddie Awad
Here is a linkthat will help answer your questions. In short:
count(*) is the correct way to write it and count(1) is OPTIMIZED TO BE count(*) internally -- since
a) count the rows where 1 is not null is less efficient than
b) count the rows
count(*) 是正确的写法，count(1) 在内部被优化为 count(*) —— 因为
a) 计算 1 不为空
的行比b) 计算行效率低
回答by Eddie Awad
Difference between count(*) and count(1) in oracle?
oracle中count(*)和count(1)的区别？
count(*) means it will count all records i.e each and every cell BUT
count(*) 表示它将计算所有记录，即每个单元格但是
count(1) means it will add one pseudo column with value 1 and returns count of all records
count(1) 表示将添加一个值为 1 的伪列并返回所有记录的计数
回答by ChrisHDog
This is similar to the difference between
这类似于
SELECT * FROM table_name and SELECT 1 FROM table_name.
If you do
如果你这样做
SELECT 1 FROM table_name
it will give you the number 1 for each row in the table. So yes count(*)and count(1)will provide the same results as will count(8)or count(column_name)
它会给你表中每一行的数字 1。所以是的count(*)，count(1)将提供与 willcount(8)或count(column_name)
回答by Cade Roux
There is no difference.
没有区别。
COUNT(1)is basically just counting a constant value 1 column for each row. As other users here have said, it's the same as COUNT(0)or COUNT(42). Any non-NULLvalue will suffice.
COUNT(1)基本上只是为每行计算一个常量值 1 列。正如这里的其他用户所说，它与COUNT(0)or相同COUNT(42)。任何非NULL值就足够了。
http://asktom.oracle.com/pls/asktom/f?p=100:11:2603224624843292::::P11_QUESTION_ID:1156151916789
http://asktom.oracle.com/pls/asktom/f?p=100:11:2603224624843292::::P11_QUESTION_ID:1156151916789
The Oracle optimizer did apparently use to have bugs in it, which caused the count to be affected by which column you picked and whether it was in an index, so the COUNT(1) convention came into being.
Oracle 优化器显然曾经在其中存在错误，这导致计数受到您选择的列以及它是否在索引中的影响，因此 COUNT(1) 约定应运而生。
回答by Thilo
SELECT COUNT(1) from <table name>
should do the exact same thing as
应该做同样的事情
SELECT COUNT(*) from <table name>
There may have been or still be some reasons why it would perform better than SELECT COUNT(*)on some database, but I would consider that a bug in the DB.
可能有或仍然有一些原因为什么它会比SELECT COUNT(*)在某些数据库上表现得更好，但我认为这是数据库中的一个错误。
SELECT COUNT(col_name) from <table name>
however has a different meaning, as it counts only the rows with a non-null value for the given column.
但是具有不同的含义，因为它只计算给定列具有非空值的行。
回答by Jarod Elliott
in oracle i believe these have exactly the same meaning
在oracle中，我相信这些具有完全相同的含义
回答by Vikas Kumar
You can test like this:
你可以这样测试：
create table test1(
id number,
name varchar2(20)
);
insert into test1 values (1,'abc');
insert into test1 values (1,'abc');
select * from test1;
select count(*) from test1;
select count(1) from test1;
select count(ALL 1) from test1;
select count(DISTINCT 1) from test1;
回答by Johann Zacharee
Depending on who you ask, some people report that executing select count(1) from random_table;runs faster than select count(*) from random_table. Others claim they are exactly the same.
取决于你问的是谁，有些人报告说执行select count(1) from random_table;运行速度比select count(*) from random_table. 其他人声称它们完全相同。
This linkclaims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.
此链接声称两者之间的速度差异是由于全表扫描与快速全扫描。
