Counting null and non-null values in a single query

sql

performance

best-practices

sql-queries

byNikita Barsukov·Dec 14, 2024

Here's the quick silver bullet to count NULL and non-NULL values in an SQL column:

SELECT 
  COUNT(column_name) AS non_nulls, /* The Dracula of SQL, afraid of NULLs! */
  COUNT(*) - COUNT(column_name) AS nulls  /* The Pac-Man of SQL, gobbles up every row! */
FROM 
  table_name;

This yields two counts: COUNT(column_name) turns a blind eye to NULLs, while COUNT(*) considers them. Subtraction gives us the ghostly count of NULLs.

Cross-platform variations

Though our fast answer is a tireless worker across platforms, remember that RDBMS-specific syntax can offer unique flavors:

Oracle supports MINUS operator, which subtracts non null rows from all rows:

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

For SQL Server, swap MINUS with EXCEPT. However, our initial fast answer performs admirably across RDBMS including MySQL and PostgreSQL.

The SUM and CASE dynamic duo

Try a tag team of SUM and CASE for more complex conditions or simply for sheer querying joy:

SELECT 
  SUM(CASE WHEN column_name IS NULL THEN 1 ELSE 0 END) AS nulls, /* Playing hide and seek with NULLs */
  SUM(CASE WHEN column_name IS NOT NULL THEN 1 ELSE 0 END) AS non_nulls /* Non-NULLs, assemble! */
FROM 
  table_name;

This dynamic duo prevails where multiple nullity checks converge.

Tackling scalability and performance

Awareness of performance is like the spice in a well-cooked dish, especially with dishes of large data:

Full table scans are SQL's equivalent of window shopping - time consuming and expensive. Peek into the execution plan to ensure you're leveraging indexes.
Cleverly named aliases improve readability, like an unexpected plot twist that keeps audience glued to a thriller.

Whether you're stirring a small salad or cooking up a divine feast, stick to the recipe of efficient SQL tailored to your specific database schema and hardware capacities.

Employing UNION ALL for distinct counts

A fan of clear distinction? Use a UNION ALL query for a visual breakout of counts for different conditions:

SELECT 'Non-nulls' AS Type, COUNT(column_name) AS Count
FROM table_name
UNION ALL
SELECT 'Nulls', COUNT(*) - COUNT(column_name)
FROM table_name;

This approach untangles the counts and eases the task of report generation or data exporting.

explain-codes / Sql / Counting null and non-null values in a single query

Linked

If statement in select (ORACLE)



Using an Alias column in the where clause in PostgreSQL



Mysql Group By and Sum total value of other column



How can I change NULL to 0 when getting a single value from a SQL function?



Sql Server equivalent of a COUNTIF aggregate function



Search an Oracle Database for Tables with Specific Column Names?



Sql Query If Parameter Is Null Select All



Cross-platform variations The SUM and CASE dynamic duo Tackling scalability and performance Employing UNION ALL for distinct counts