Section 2.3. The COUNT Function

2.3. The COUNT Function

The COUNT function is used to return a count of the number of rows that the output will produce, without actually displaying all of the output (rows) themselves. This function often comes in handy when you have large tables, or you expect a large output. In such situations, it is desirable to determine the number of rows of output that you will be getting before actually displaying the output. In this section, we introduce the COUNT function and we also take another look at the concept of null values.

If you type the following command:

SELECT *
FROM   Dependent

you will get an output that includes all the rows of the Dependent table plus all the values for all columns in those rows. If you want to know only the number of rows in the output (rather than view the actual rows themselves), type the following:

SELECT COUNT(*)
FROM   Dependent

This query produces the following output:

-----------
39
 
(1 row(s) affected)

This output says that there are 39 rows in the Dependent table. Note that the actual rows themselves are not displayed.

It is often useful to count the occurrence of column values that have a value. For example, suppose we want to find how many nonnull rows are in a particular column. With this query:

SELECT COUNT(age)
FROM   Dependent

we get:

-----------
36
 
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row(s) affected)

COUNT(age) counts only the rows in which age is not null, meaning that it counts only the rows that have a defined value. Therefore, the preceding output is 36 rows rather than 39 rows because the age column in the Dependent table includes 3 null values. If you want COUNT to count rows and include rows that have fields with null values, you would use COUNT(*). In the next section, we discuss null values in more detail.

2.3.1. IS NULL

Null values are used to designate missing data in columns. The IS NULL condition is the only condition that directly tests for nulls. Null values are unmatched by all other conditions in WHERE clauses. Rows with null values cannot be retrieved by using = NULL in a WHERE clause, because NULL signifies a missing value. No value is considered to be equal to, greater than, or less than NULL. Even a space is not considered to be a NULL, and a null is not considered to be a space. Nulls are not considered like any other value in a table either, since nulls do not have data types. Also, because nulls do not have data types, there is no distinction between nulls in numeric columns and nulls in text columns or date columns.

The following query provides dependent names and the ages of dependents (from the Dependent table) that have null values for their age columns:

SELECT dname, age
FROM   Dependent
WHERE  age IS NULL

This produces the following three rows of output:

dname                age
-------------------- ------
Donald II            NULL
Mita                 NULL
Losmith              NULL
 
(3 row(s) affected)

2.3.2. IS NOT NULL

To retrieve all rows that are not nulls, IS NOT NULL can be used. The following query will give all the rows that are not nullsthe remaining 36 rows of the table (of which we show the first 10 rows):

SELECT dname, age
FROM   Dependent
WHERE  age IS NOT NULL

which produces 36 rows of output (of which the first 10 rows are shown):

dname                age
-------------------- ------
Matt                 8
Mary                 9
Beena                31
Amit                 3
Shantu               5
Raju                 1
Rani                 3
Susan                4
Sam                  1
Chris                6
.
.
.
 
(36 row(s) affected)