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
Solved! Go to 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
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
Thanks Dave,
i was asked both questions in an Interview recently & was wondering what the difference is?
Thanks Peter
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)
Thanks for the extra info, Cheers
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 ...)