Oracle & SQL Server: COUNT(*) vs. COUNT(1) vs. COUNT(ColumnName) – Differences

NULL’s will be eliminated!

When doing a count() operation, it helps to understand what is counted and what is not.

Count can be done in multiple ways

  • COUNT(*)
  • COUNT(1)
  • COUNT([Column_Name])
  • COUNT(DISTNCT [Column_Name])
  • With and without GROUP BY

While it is straight-forward to get a count, here are a couple of things to note

  • NULL values are eliminated from being COUNTed.
  • There can be subtle differences in behavior between SQL Server and Oracle (more below)

Sample Data

Let us illustrate with an example:

This is the data we are going to be dealing with:

EMPID EMPNAME ADDRESS ANNUALSALARY
1 Mike 60000
2 Ed 123 Main Street
3 Will 45 E.Lock Street 80000
4 Brad 1 32nd Street 50000
5 Alex 601 Broadway 60000
6 Sally
7 Tom 50000

If testing with Oracle, use:

WITH Emp
AS
(
    SELECT 1 AS EmpId, 'Mike' AS EmpName, NULL AS Address, 60000 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 2 AS EmpId, 'Ed' AS EmpName, '123 Main Street' AS Address, NULL AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 3 AS EmpId, 'Will' AS EmpName, '45 E.Lock Street' AS Address, 80000 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 4 AS EmpId, 'Brad' AS EmpName, '1 32nd Street' AS Address, 50000 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 5 AS EmpId, 'Alex' AS EmpName, '601 Broadway' AS Address, 60000 AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 6 AS EmpId, 'Sally' AS EmpName, NULL AS Address, NULL AS AnnualSalary
    FROM DUAL
    UNION ALL
    SELECT 7 AS EmpId, 'Tom' AS EmpName, '' AS Address, 50000 AS AnnualSalary
    FROM DUAL
)
SELECT
    COUNT(*),
    COUNT(1),
    COUNT(Address),
    COUNT(EmpId),
    COUNT(DISTINCT EmpId),
    COUNT(AnnualSalary),
    COUNT(DISTINCT AnnualSalary)
FROM
    Emp

Results:

COUNT(*) COUNT(1) COUNT(ADDRESS) COUNT(EMPID) COUNT(DISTINCTEMPID) COUNT(ANNUALSALARY) COUNT(DISTINCTANNUALSALARY)
7 7 4 7 7 5 3

Observations:

  • COUNT(*) and COUNT(1) produce the same result
  • COUNT() using a column name only counts the number of non-null values
    • Notice that we get COUNT(Address) as 4 while there are 7 rows
  • COUNT(DISTINCT [ColumnName]) also first eliminates NULL’s and then counts the distinct values
    • If NULL was counted as one of the distinct values COUNT(DISTINCT AnnualSalary) would have had a value of 4 but it has a value of 3
  • ” (or) empty string is considered a NULL and treated practically as NULL in Oracle for all string operations

If testing with SQL Server, use:

WITH Emp
AS
(
    SELECT 1 AS EmpId, 'Mike' AS EmpName, NULL AS Address, 60000 AS AnnualSalary
    UNION ALL
    SELECT 2 AS EmpId, 'Ed' AS EmpName, '123 Main Street' AS Address, NULL AS AnnualSalary
    UNION ALL
    SELECT 3 AS EmpId, 'Will' AS EmpName, '45 E.Lock Street' AS Address, 80000 AS AnnualSalary
    UNION ALL
    SELECT 4 AS EmpId, 'Brad' AS EmpName, '1 32nd Street' AS Address, 50000 AS AnnualSalary
    UNION ALL
    SELECT 5 AS EmpId, 'Alex' AS EmpName, '601 Broadway' AS Address, 60000 AS AnnualSalary
    UNION ALL
    SELECT 6 AS EmpId, 'Sally' AS EmpName, NULL AS Address, NULL AS AnnualSalary
    UNION ALL
    SELECT 7 AS EmpId, 'Tom' AS EmpName, '' AS Address, 50000 AS AnnualSalary
)
SELECT
    COUNT(*),
    COUNT(1),
    COUNT(Address),
    COUNT(EmpId),
    COUNT(DISTINCT EmpId),
    COUNT(AnnualSalary),
    COUNT(DISTINCT AnnualSalary)
FROM
    Emp

Results

COUNT(*) COUNT(1) COUNT(ADDRESS) COUNT(EMPID) COUNT(DISTINCTEMPID) COUNT(ANNUALSALARY) COUNT(DISTINCTANNUALSALARY)
7 7 5 7 7 5 3

Observations:

  • Everything is the same as Oracle (other than FROM DUAL elimination) except COUNT(Address) which is highlighted above.
  • Empty strings (”) are NOT treated as NULL’s in SQL Server (unlike Oracle)

Myths:

There is a common misconception that doing COUNT(*) takes more resources and is hence slower too than doing COUNT(1). This is a myth in both the SQL Server world and the Oracle world. Optimizers are extremely smart these days and they compensate for trivial things like these.  If you look at the explain plan and the traces files, there should be no difference in the plan as well as the amount of I/O involved.

Watch out for NULL’s when COUNTing!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s