COUNT(*) vs COUNT(column) difference in SQL

Introduction

COUNT(*) counts all rows, while COUNT(column) counts only rows where the column has a value. This helps you understand data completeness.

When you want to know how many rows are in a table regardless of missing data.

When you want to count only rows where a specific column has a value (not NULL).

When checking how many entries have data in a particular column.

When comparing total rows versus rows with valid data in a column.

When summarizing data completeness in reports.

Syntax

SQL

SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name;

COUNT(*) counts every row, including those with NULLs in any column.

COUNT(column_name) counts only rows where column_name is NOT NULL.

Examples

Counts all rows in the employees table.

SQL

SELECT COUNT(*) FROM employees;

Counts only rows where the email column is not NULL.

SQL

SELECT COUNT(email) FROM employees;

Shows total rows and how many have a phone number.

SQL

SELECT COUNT(*) AS total_rows, COUNT(phone) AS phone_count FROM contacts;

Sample Program

This creates a table with some NULL emails and counts total rows and rows with emails.

SQL

CREATE TABLE people (
  id INT,
  name VARCHAR(50),
  email VARCHAR(50)
);

INSERT INTO people (id, name, email) VALUES
(1, 'Alice', '[e-mail]'),
(2, 'Bob', NULL),
(3, 'Charlie', '[e-mail]'),
(4, 'Diana', NULL);

SELECT COUNT(*) AS total_rows, COUNT(email) AS email_count FROM people;

Important Notes

COUNT(*) is faster when you want total rows because it doesn't check column values.

COUNT(column) ignores NULLs, so it helps find how many rows have actual data in that column.

NULL means no value, so COUNT(column) skips those rows.

Summary

COUNT(*) counts all rows, including those with NULLs.

COUNT(column) counts only rows where the column is NOT NULL.

Use COUNT(column) to find how many rows have data in that column.