SQL NULL, COUNT and COALESCE: handle missing information

Missing is a separate state

Kiran's city is NULL in the sample dataset. That means the city value is missing; it is not the city named NULL and not an empty string. Keeping those meanings separate matters when counting completed records or grouping an audience by location. A missing mark would also be different from a genuine mark of zero.

Ordinary equality comparisons involving NULL do not work like equality between two known city strings. Use IS NULL or IS NOT NULL to test for a missing value. Writing city = NULL does not select the missing-city record in this dataset. The WHERE condition must evaluate as true for a row to be retained.

Ask what each count includes

COUNT(*) counts all rows in the selected population. COUNT(city) counts rows where city is non-null. COUNT(DISTINCT city) counts distinct known city values. The sample therefore produces six students, five known cities at the row level and three distinct known city names. These are different metrics, even though each uses the word count.

Do not label COUNT(city) as total students. A report could look plausible while quietly excluding the records most in need of data completion. If the report is intended to measure completeness, compute both total and known counts and describe the denominator. A later WHERE filter changes that denominator and must be reflected in the label.

Use a fallback without rewriting the stored facts

COALESCE(city, 'Not provided') returns the first non-null expression for display. It does not update the city field. This is useful for an understandable report, but a display label is not a real location. Do not send Not provided into a map or claim it represents a verified city.

An empty string is not NULL in SQLite, so COALESCE alone does not replace it. If an application allows blank strings as well as missing values, define a normalization policy explicitly. This lesson keeps the input states simple so you can verify each result before handling whitespace, inconsistent spelling or other data-cleaning rules.

Run the worked example

PRAGMA foreign_keys = ON;
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    cohort TEXT NOT NULL
);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
    (1, 'Asha', 'Jaipur', 82, 'Java'),
    (2, 'Aman', 'Ajmer', 66, 'Java'),
    (3, 'Jaya', 'Jaipur', 94, 'SQL'),
    (4, 'Kiran', NULL, 48, 'SQL'),
    (5, 'Neha', 'Kota', 82, 'SQL'),
    (6, 'Kabir', 'Jaipur', 40, 'Java');
INSERT INTO courses VALUES (10, 'Java'), (20, 'SQL'), (30, 'Python');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10), (3, 20), (5, 20);

Download setup.sql

SELECT COUNT(*) AS total_students,
       COUNT(city) AS students_with_city,
       COUNT(DISTINCT city) AS distinct_known_cities
FROM students;

Expected result

1. All six rows contribute to COUNT(*).
2. Kiran's null city is excluded from COUNT(city).
3. Repeated Jaipur values count once in COUNT(DISTINCT city).

Try it yourself

Find only students whose city is missing. Return name and COALESCE(city, 'Not provided') as city_label, ordered by ID.

SELECT name, COALESCE(city, 'Not provided') AS city_label
FROM students WHERE city IS NULL ORDER BY id;

Quick check

Choose an answer, then check the explanation. Your answer stays in this page and is not saved as a student record.

Keep learning

Explain the result in your own words, change one input and predict the new output. For additional structured assignments, explore the workbook sample before requesting the full pack.