Re: SELECT COUNT(*)

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 07:18:38 -0700

That is a good question. I'd only guess it's something
implemented similar to Oracle to allows things such as
select GetDate() without the from clause. The from clause is
suppose to be required in every select statement.
In Oracle, the from is always required so if you used their
implementation of the same and used dual, select count(*)
from dual should return 1 if I remember correctly.
In other words, I'd guess SQL Server and Sybase implement
something similar to the dummy table dual but you just don't
have to be explicit with it such as you do in Oracle.

-Sue

On Tue, 15 Feb 2005 05:03:03 -0800, "Mike Epprecht (SQL
MVP)" <mike@epprecht.net> wrote:

>Hi
>
>select count(*) where 1=2
>go
>
>select count(*)
>go
>
>Results:
>-----------
>0
>
>-----------
>1
>
>What is "select count(*)" counting ?
>
>Where 1=1 ?
>
>Same behavior in SQL 7.0, 2000 and Sybase. Oracle, it does not work with
>error "Missing FROM clause".
>
>--------------------------------
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>MVP Program: http://www.microsoft.com/mvp

Relevant Pages

  • Re: Locking question when using Select clause with For Update and Skip locked
    ... table for update and skipped the rows locked by other sessions on same ... select top 1 empno from emp ... (we also have order by clause but will remove it here for simplicity) ... Now on Oracle system it is ...
    (comp.databases.oracle.server)
  • Re: Outer join by (+)
    ... Robert Klemme wrote: ... I can read the standard SQL just fine, because that's what I'm used to. ... WHERE clause excludes. ... This still leaves the issue of lesser expressiveness for the proprietary Oracle syntax. ...
    (comp.databases.oracle.server)
  • Re: How top actually works
    ... So the next one shows that order by clause has affected the result set ... sort these N rows according to my order by clause. ... Ie. you are telling SQL Server ... This is much the same as with rownum in Oracle, ...
    (comp.databases.ms-sqlserver)
  • How top actually works
    ... I'm coming from Oracle world and trying to find something similar to ... So the next one shows that order by clause has affected the result set ... and actually semms to be pushed into inner query. ... the user just ANY N rows satisfying criteria. ...
    (comp.databases.ms-sqlserver)
  • Re: select on a view -> ORA-12571
    ... But if I use the field VID_SBA inside the where- clause I get an error ORA-12571 an the connection to DB is closed. ... Oracle Customer Support. ... If Oracle is installed second time and the error is up and away and I can't reproduce it my application will be delivered without any guarantee whether the problem is solved or not! ... Laurenz Albe ...
    (comp.databases.oracle.misc)