0Pricing
SQL Interview Prep · Lesson

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

The classic question on how each COUNT form treats NULLs and duplicates.

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT) is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question You Will Be Asked

If you sit a SQL interview, expect this within the first ten minutes: What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

It looks trivial, but interviewers use it to check whether you truly understand how aggregates treat NULL values and duplicates. A confident, precise answer signals you have written real queries, not just memorized syntax.

In this lesson we will build a rock-solid mental model so you can answer instantly and explain the why.

COUNT(*) Counts Rows

COUNT(*) counts rows. It does not look at any column values at all, so NULLs are irrelevant. If the table has 100 rows, COUNT(*) returns 100, period.

The asterisk is not multiplying or expanding columns here. It is a special token meaning "count every qualifying row." This is the form you reach for when the question is simply "how many records?"

SELECT COUNT(*) AS total_rows
FROM employees;

Sample Data to Reason About

Let's anchor everything to one small table. Imagine an employees table with a nullable department column:

  • Alice, Sales
  • Bob, Sales
  • Carol, NULL
  • Dan, Engineering
  • Eve, NULL

That is 5 rows total. Three rows have a department value (Sales, Sales, Engineering) and two rows have NULL. Keep these numbers in mind; we will count this table three different ways.

COUNT(column) Skips NULLs

COUNT(column) counts only the rows where that column is not NULL. It ignores NULL values entirely.

On our sample data, COUNT(department) returns 3, not 5, because two department values are NULL. This is the single most important fact in the whole lesson: COUNT of a column never counts NULLs.

SELECT COUNT(department) AS depts_present
FROM employees;
-- returns 3 on our sample data

Comparing the Two So Far

Side by side on the same 5-row table:

  • COUNT(*) = 5 (every row)
  • COUNT(department) = 3 (non-NULL departments)

The gap between them, 5 minus 3, equals the number of NULL departments. A neat interview trick: COUNT(*) - COUNT(col) gives you the count of NULLs in that column without writing a separate WHERE clause.

SELECT COUNT(*) - COUNT(department) AS null_departments
FROM employees;
-- returns 2

COUNT(DISTINCT column)

COUNT(DISTINCT column) counts the number of unique non-NULL values. It first removes duplicates, then ignores NULLs, then counts what remains.

On our data the distinct department values are Sales and Engineering, so COUNT(DISTINCT department) returns 2. The two NULLs do not count, and the duplicate Sales collapses to one.

SELECT COUNT(DISTINCT department) AS unique_depts
FROM employees;
-- returns 2

All Three Together

The full picture on our 5-row table:

  • COUNT(*) = 5 — rows
  • COUNT(department) = 3 — non-NULL values
  • COUNT(DISTINCT department) = 2 — unique non-NULL values

If you can recite this ordering, NULL-aware then duplicate-aware, you have the whole concept. Notice each form returns a value less than or equal to the one above it.

SELECT
  COUNT(*)                       AS rows_total,
  COUNT(department)              AS depts_present,
  COUNT(DISTINCT department)     AS unique_depts
FROM employees;

A Worked Interview Variant

Interviewers often dress this up: "How many employees are assigned to a department, and how many distinct departments exist?"

The first half is COUNT(department) (assigned means non-NULL). The second half is COUNT(DISTINCT department). Recognizing which COUNT each phrase maps to is exactly the skill being tested.

SELECT
  COUNT(department)          AS assigned_employees,
  COUNT(DISTINCT department) AS distinct_departments
FROM employees;

COUNT With a Condition

A favorite follow-up: count only rows meeting a condition. A clean, portable trick is COUNT over a CASE expression. Because COUNT(column) skips NULLs, make the CASE return NULL for rows you want to exclude.

Here we count Sales employees. The CASE yields a value only for Sales rows and NULL otherwise, so COUNT tallies just those.

SELECT
  COUNT(CASE WHEN department = 'Sales' THEN 1 END) AS sales_count
FROM employees;
-- returns 2

Common Mistakes to Avoid

Watch for these traps that interviewers plant:

  • Assuming COUNT(column) equals COUNT(*) — it only does when the column has zero NULLs.
  • Thinking COUNT(DISTINCT col) includes NULL as one distinct value — it does not.
  • Writing COUNT(1) expecting it to skip NULLs — it behaves like COUNT(*), counting all rows.

State these proactively in an interview to show depth.

COUNT(1) and COUNT(constant)

Candidates often ask which is faster, COUNT(*) or COUNT(1). Answer: they are semantically identical — both count every row regardless of NULLs — and modern optimizers treat them the same, so there is no real performance difference.

The constant inside COUNT(1) is never NULL, so no row is ever skipped. Prefer COUNT(*) for clarity; it is the idiomatic form.

SELECT COUNT(*) AS a, COUNT(1) AS b
FROM employees;
-- a and b are always equal

Quick Check

Test your mental model on the sample table.

Recap

You now own the most-asked aggregate question:

  • COUNT(*) counts every row, NULLs included.
  • COUNT(column) counts non-NULL values only.
  • COUNT(DISTINCT column) counts unique non-NULL values.
  • COUNT(*) - COUNT(col) gives the NULL count for free.
  • COUNT(1) equals COUNT(*) — no NULL skipping, no speed difference.

Say it crisply and back it with the why. Next up: how SUM and AVG handle NULLs.

Frequently asked questions

Is the “COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)” lesson free?

Yes — the full text of “COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)”?

The classic question on how each COUNT form treats NULLs and duplicates. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
  2. SUM and AVG with NULLs
  3. MIN, MAX and Non-Numeric Aggregation
  4. Aggregates Without GROUP BY
← Back to SQL Interview Prep