[sql] What does "select count(1) from table_name" on any database tables mean?

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signify here? Is this the same as count(*) (as it gives the same result on execution)?

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.

This link claims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.

Difference between count(*) and count(1) in oracle?

count(*) means it will count all records i.e each and every cell BUT

count(1) means it will add one pseudo column with value 1 and returns count of all records

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

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)

in oracle i believe these have exactly the same meaning

Here is a link that 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

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.

This link claims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.

Difference between count(*) and count(1) in oracle?

count(*) means it will count all records i.e each and every cell BUT

count(1) means it will add one pseudo column with value 1 and returns count of all records

in oracle i believe these have exactly the same meaning

Here is a link that 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

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-NULL value will suffice.

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.

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)

Difference between count(*) and count(1) in oracle?

count(*) means it will count all records i.e each and every cell BUT

count(1) means it will add one pseudo column with value 1 and returns count of all records

Here is a link that 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

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

Here is a link that 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

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

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;

Difference between count(*) and count(1) in oracle?

count(*) means it will count all records i.e each and every cell BUT

count(1) means it will add one pseudo column with value 1 and returns count of all records

in oracle i believe these have exactly the same meaning

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-NULL value will suffice.

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.

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)

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-NULL value will suffice.

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.

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.

This link claims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.

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;

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.

This link claims that the speed difference between the 2 is due to a FULL TABLE SCAN vs FAST FULL SCAN.

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