SQL Interview Questions · Question 15

A table contains duplicate and NULL email values. How will COUNT(*), COUNT(email), and COUNT(DISTINCT email) differ?

Interview preparation resource from Gate Smashers.

Interview-ready answer

COUNT(*) counts all rows; COUNT(email) counts only non-NULL email values; COUNT(DISTINCT email) counts unique non-NULL email values.

Example SQLsql

SELECT COUNT(*) AS total_rows,
       COUNT(email) AS non_null_emails,
       COUNT(DISTINCT email) AS unique_non_null_emails
FROM Customers;

Understand it clearly

Direct answer

COUNT(*) counts rows in the result set. COUNT(email) counts only non-NULL values in the email column. COUNT(DISTINCT email) counts unique non-NULL email values (duplicates removed).

**COUNT(*):** Counts every row, regardless of column values or NULLs.
COUNT(email): Counts only non-NULL email entries; ignores NULLs.
COUNT(DISTINCT email): Counts distinct (unique) non-NULL email values; duplicates are removed before counting.

How each function works

COUNT(*) does not inspect any particular column

it simply counts result rows. Aggregate functions that take a column as an argument ignore NULL values for that column. DISTINCT removes duplicate non-NULL values before the aggregate is applied.

NULL and duplicate handling

NULL values are ignored by column-based aggregates (so COUNT(email) and COUNT(DISTINCT email) do not count NULLs). Duplicate non-NULL email values are counted by COUNT(email) but are removed by COUNT(DISTINCT email), so only unique non-NULL emails contribute to the DISTINCT count.

Example query

The following query shows all three counts together; aliases label each result column for clarity. The actual SQL is provided below in the code block.

Quick comparison

BasisCOUNT(*)COUNT(email) / COUNT(DISTINCT email)

What is countedAll rows in the result setCOUNT(email): non-NULL email values; COUNT(DISTINCT email): unique non-NULL email values

NULL handlingCounts rows regardless of NULLsNULLs are ignored by both email-based counts (they do not contribute to the count)

Duplicate handlingDuplicates are included (every row counted)COUNT(email): duplicates counted; COUNT(DISTINCT email): duplicates removed before counting

Direct answer

How each function works

COUNT(*) does not inspect any particular column

NULL and duplicate handling

Example query

Related SQL Interview Questions questions