SQL NULL and COUNT explained with a club survey

Mira collects book counts from her reading club. There are five members, including her. One reports zero books. Two have not answered yet. When she puts their responses into a database, she wants to keep that difference: zero is an answer, while a missing answer is still unknown.

SQL uses NULL to represent a missing or unknown value. Treating it like zero can turn an unfinished survey into a confident but misleading report. In this lesson, you will build a tiny SQLite table and learn which question each form of COUNT actually answers.

You should know what a basic SELECT query does. You can follow the table and results here without installing anything. Running the examples is optional and requires a SQLite environment.

Start with five survey rows

Run the SQL blocks in order in a fresh SQLite database:

CREATE TABLE reading_survey (
    student TEXT PRIMARY KEY,
    books INTEGER
);

INSERT INTO reading_survey VALUES
    ('Mira', 3),
    ('Dev', 0),
    ('Leena', NULL),
    ('Omar', 3),
    ('Zoya', NULL);

There are five students, but only three recorded book counts. Dev's zero belongs among those three answers. Leena and Zoya might eventually report zero, ten, or another number. The table does not tell us yet.

Write NULL without quotation marks. The text 'NULL' would be a string, not the missing-value marker. That small spelling difference changes what the database stores.

Ask three different counting questions

SELECT
    COUNT(*) AS students,
    COUNT(books) AS answered,
    COUNT(DISTINCT books) AS different_answers
FROM reading_survey;

The result is:

students | answered | different_answers
5        | 3        | 2

COUNT(*) counts rows. COUNT(books) counts non-NULL values in that column. COUNT(DISTINCT books) counts distinct non-NULL values, which here are zero and three. These rules come directly from SQLite's aggregate-function documentation.

Notice how different those questions are. “How many students are in the survey?” needs five. “How many answered?” needs three. “How many different answers did we receive?” needs two. None of these results is a replacement for the others.

In this table, COUNT(student) also returns five because every stored student name is present. That agreement is a property of these rows, not a reason to use an arbitrary column when you mean to count rows.

Find the people who have not answered

It is tempting to write WHERE books = NULL. However, an ordinary comparison with NULL produces an unknown result rather than true. A WHERE filter keeps rows whose condition is true, so that comparison does not find the missing answers. Use IS NULL instead. See SQLite's expression rules and WHERE filtering rules.

SELECT student
FROM reading_survey
WHERE books IS NULL
ORDER BY student;

This returns Leena and Zoya. To find recorded answers, use IS NOT NULL. To find students who explicitly answered zero, use books = 0.

Here is a compact completeness check:

SELECT COUNT(*) - COUNT(books) AS missing_answers
FROM reading_survey;

It returns two. Mira could put “three of five answered” above her report, making its limits visible before anyone interprets the numbers.

Imagine the club wants to contact everyone who has not replied. Sending that reminder to Dev would be a mistake: he did reply. Keeping zero separate from missing information helps the query match the real task, instead of making people do extra work because the report used the wrong definition.

Try it yourself

Leena now reports two books. Before running this, predict all three counts:

UPDATE reading_survey
SET books = 2
WHERE student = 'Leena';

SELECT COUNT(*), COUNT(books), COUNT(DISTINCT books)
FROM reading_survey;

Answer: five students, four recorded answers, and three different answers: zero, two, and three. Only Zoya remains unanswered.

Before choosing a counting function, finish this sentence: “I need the number of …” If the answer is rows, use COUNT(*). If it is recorded values, name the column. That habit makes missing information harder to hide accidentally.

Optional: a counting shortcut to avoid

One more trap is COUNT(books = 0). For the known answers, the comparison produces either true or false; both are non-NULL and therefore counted. If the goal is to count zero-book answers, filter with WHERE books = 0 and then use COUNT(*).