select count(*);

October 26th, 2009

Quick trivia: what is the result of running SELECT COUNT(*);?

That’s right, no FROM clause, just COUNT(*). The answer may be a little bit surprising, is 1. When you query SELECT 1; the result is, as expected, 1. And SELECT 2; will return 2. So SELECT COUNT(2); returns, as expected, 1, after all it counts how many rows are in the result set. But SELECT COUNT(*); has a certain smell of voo-doo to it. Ok, is the * project operator, but project from… what exactly? It feels eerie, like a count is materialized out of the blue.

How about SELECT COUNT(*) [MyTable]. Well, that’s actually just a shortcut for SELECT COUNT(*) AS [MyTable], so it still returns 1 but in a column named MyTable. Now you understand why my heart missed a bit when I checked how I initialized a replication subscription and I forgot to type in FROM…

Posted in Samples

3 responses to “select count(*);”

  1. Madhivanan says:
    October 27, 2009 at 12:13 am

    This is the reson I strongly suggest the usage of AS before alias name http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/14/should-alias-names-be-preceded-by-as.aspx

  2. Joao Polisel says:
    March 4, 2010 at 10:48 am

    When I used to work with DB2 on iSeries, I remeber that a simple “SELECT COUNT(*)” wouldn’t work. Not event a simple “SELECT CURRENT_TIMESTAMP”.
    On that DBMS you must always specify the FROM clause, and there’s a table on the SYSIBM library just to deal with this situation. The table is called SYSDUMMY1 and has just one row, so the right way of getting the system data was “SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1”.

  3. Nitin says:
    March 9, 2010 at 5:24 pm

    I suggest you use ssms tools pack from Mladen.

    Its a great freeware