10 Replies Latest reply: Apr 28, 2010 10:12 PM by Tubby RSS

    count(1) vs count(*)

    user520824
      all, what is the difference between count(1) and count(*).
        • 1. Re: count(1) vs count(*)
          548849
          There is no difference. it's just a myth....
          • 2. Re: count(1) vs count(*)
            Centinul
            Obligatory AskTom link:

            Select Count(1): How it works
            • 3. Re: count(1) vs count(*)
              534518
              count(1) returns the count of the first column in the table. count(*) returns number of records in a table:
              • 4. Re: count(1) vs count(*)
                ajallen
                COUNT(1) is used by people who believe that it runs faster than COUNT(*), or out of tradition or habit.
                COUNT(*) is used by people who understand that, internally, COUNT(1) is the same as COUNT(*).

                So, all in all, WTF cares?
                • 5. Re: count(1) vs count(*)
                  Dave Rabone
                  You need to read the AskTom link too ...

                  I've never seen that variation of the myth before, your statement is just wrong.
                  • 6. Re: count(1) vs count(*)
                    535004
                    Hi,

                    Both are same and retrieve the same output, some person use 1 because they think 1 give the out quickly than *
                    • 7. Re: count(1) vs count(*)
                      TheOtherGuy
                      to answer original question without heating up the discussion ....


                      there is no speed difference - assuming you don't use Oracle 2.0 ;-) and personally I always used count(*) because the query visually looks nicer "I count all records" instead of "i count digit 1"

                      but

                      there is difference when it comes to behavior, specifically when it comes to views and oracle 11g - this is probably rare case, but still you can see there is a difference (again this is behavior, not speed).

                      code below was executed on 11.2 on redhat exterprise linux 5
                      CREATE TABLE MY_TEST AS SELECT * FROM DUAL;
                      
                      CREATE VIEW VIEW_COUNT_1    AS SELECT COUNT(1) COUNTER FROM MY_TEST;
                      CREATE VIEW VIEW_COUNT_STAR AS SELECT COUNT(*) COUNTER FROM MY_TEST;
                      
                      SELECT OBJECT_NAME, STATUS  FROM USER_OBJECTS WHERE (OBJECT_NAME LIKE 'VIEW_COUNT%') ;
                      
                      ALTER TABLE MY_TEST add (description varchar2(60));
                      
                      SELECT OBJECT_NAME, STATUS  FROM USER_OBJECTS WHERE (OBJECT_NAME LIKE 'VIEW_COUNT%') ;
                      as you can see view which used count(1) is in invalid state on 11g, I tested this on 10g and both views got into invalid state on 10g (I used XE for testing)

                      Table created.
                      View created.
                      View created.
                      
                      
                      OBJECT_NAME      STATUS
                      ---------------------------
                      VIEW_COUNT_1     VALID
                      VIEW_COUNT_STAR  VALID
                      
                      
                      Table altered.
                      
                      
                      OBJECT_NAME      STATUS
                      ---------------------------
                      VIEW_COUNT_1     INVALID
                      VIEW_COUNT_STAR  VALID
                      • 8. Re: count(1) vs count(*)
                        SomeoneElse
                        count(1) returns the count of the first column in the table. count(*) returns number of records in a table:
                        That's a new one.
                        SQL> select count(1) from emp;
                        
                                    COUNT(1)
                        --------------------
                                          14
                        
                        SQL> select count(0) from emp;
                        
                                    COUNT(0)
                        --------------------
                                          14
                        
                        SQL> select count(-1) from emp;
                        
                                   COUNT(-1)
                        --------------------
                                          14
                        
                        SQL> select count(42) from emp;
                        
                                   COUNT(42)
                        --------------------
                                          14
                        
                        SQL> select count(*) from emp;
                        
                                    COUNT(*)
                        --------------------
                                          14
                        
                        SQL> select count('Chocula') from emp;
                        
                            COUNT('CHOCULA')
                        --------------------
                                          14
                        • 9. Re: count(1) vs count(*)
                          pkchan
                          count(column_name) => counts non-null data
                          count(n) = count(*) => counts all rows

                          select count(1) from emp; 14
                          select count(7) from emp; 14
                          select count(comm) from emp; 4
                          select count(empno) from emp; 14
                          • 10. Re: count(1) vs count(*)
                            Tubby
                            SomeoneElse wrote:
                            count(1) returns the count of the first column in the table. count(*) returns number of records in a table:
                            That's a new one.

                            SQL> select count('Chocula') from emp;

                            COUNT('CHOCULA')
                            --------------------
                            14

                            You just made my night ... thanks man :)