Select count(1) from Table_X meaning

[OscarBoots]

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 

[DaveWellman · Accepted solution]

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

[OscarBoots]

Thanks Dave,

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

Thanks Peter

[dnoeth]

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)

[OscarBoots]

Thanks for the extra info, Cheers

[Waldar]

---

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