Select count(1) from Table_X meaning

General
Highlighted
Enthusiast

Select count(1) from Table_X meaning

Hi Forum,

 

Quick question, I know that Select count(*) from Table_X means select all records from Table_X, but what does Select count(1) from Table_X do?

Is it a count of all records in the first Column and if it was count(2) it would be the second column? 

If so, does it need a Distinct in order to give a count without duplicates?

 

Thanks 


Accepted Solutions
Senior Apprentice

Re: Select count(1) from Table_X meaning

Hi,

 

Firstly, "SELECT count(*)  FROM table;" means count the number of records - not 'select all records'. This query returns a single row/column as the answer set whereas 'select all records' (SELECT * FROM table) would return ne answer set row for every row in the table.

"Select count(1) from Table_X" will also return a single row/column with the same meaning. On current versions of Teradata the two will perform the same. On older releases you may find that this one performs worse (than 'select count(*)').

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES
Senior Apprentice

Re: Select count(1) from Table_X meaning

Hi,

 

Firstly, "SELECT count(*)  FROM table;" means count the number of records - not 'select all records'. This query returns a single row/column as the answer set whereas 'select all records' (SELECT * FROM table) would return ne answer set row for every row in the table.

"Select count(1) from Table_X" will also return a single row/column with the same meaning. On current versions of Teradata the two will perform the same. On older releases you may find that this one performs worse (than 'select count(*)').

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Select count(1) from Table_X meaning

Thanks Dave,

 

i was asked both questions in an Interview recently & was wondering what the difference is?

 

Thanks Peter

Junior Contributor

Re: Select count(1) from Table_X meaning

Addng to Dave's answer:

The 1 is the literal value one, as count counts non-NULL values it could be any other value, 0, -9999, 'bla', current_date (of course not NULL), but everyone uses the magic number 1 :-)

Some people will tell you that count(1) is better/faster/recommended, but this is no longer true in all major DBMSes (in some it was never true).

 

It's similar to the famous where exists (select 1 ...) vs. where exists (select * ...):

There's no difference, it's never actually used, otherwise where exists (select 1/0 ...) would fail.

But I avoid * when it's part of a View since I noticed that the stupid optimizer actually resolves it to the full list of columns in the internal RequestText (returned when you run SHOW QUALIFIED SELECT * FROM view_with_exists)

Enthusiast

Re: Select count(1) from Table_X meaning

Thanks for the extra info, Cheers

Teradata Employee

Re: Select count(1) from Table_X meaning


It's similar to the famous where exists (select 1 ...) vs. where exists (select * ...):

There's no difference, it's never actually used, otherwise where exists (select 1/0 ...) would fail.


To emphasize this aspect, I always code the [not] exists clause this way :

 where exists (select null from ...)