SQL Count not null values in every table column

In this post:

  • SQL count null and not null values for several columns
  • MySQL select count null values per column
    • Count by multiple selects
  • MySQL count values for every table and schema
  • Oracle SQL select count null values per column
    • Count by multiple selects
    • Count by single select query
    • Oracle count null and not null values for several columns

If you need to check the number of null values per column for tables with many columns or for many tables you can use meta database meta information. In most databases there is technical and statistical information. Despite that you may need to review this faster - for example after huge data import or another event.

MySQL select count null values per column

Count by multiple selects

  • for table named person with owner powner generate SQL query which counts all values(not null) per column.

  • Set concatenation by pipe(in order to work || concatenation):

set sql_mode=PIPES_AS_CONCAT;
  • Run this SQL (first replace POWNER and PERSON with your names)
SELECT 
'select count(' || 
column_name ||
 '),     ''' ||
 column_name ||
 ''' from powner.person union' AS table_name
FROM information_schema.columns
WHERE table_schema = 'powner' 
AND table_name = 'person'
  • Copy the result and paste it in new Query tab

  • Delete the last union and run the result query

select count(id), 'id' from powner.person union
select count(version), 'version' from powner.person 
  • Result
Count(id) id
10 id
10 version
7 name

MySQL count values for every table and schema

You can count the null or not null values for every table and schema in MySQL. This can be done in two step process.

  • First lets prepare script reading every table and column in MySQL instance:
set sql_mode=PIPES_AS_CONCAT;

SELECT 
'select count(' || 
column_name ||
 '),     ''' ||
table_schema || ''',''' || table_name || ''',''' ||column_name ||
 ''' from '|| table_schema || '.' || table_name || ' union' AS table_name
FROM information_schema.columns

The result will be another query which should be executed:

select count(id),     'test','aboys','id' from test.aboys union
select count(name),     'test','aboys','name' from test.aboys union
select count(relation),     'test','aboys','relation' from test.aboys
...

You need to clean it up a bit:

  • remove the final union

  • remove all reserved keyword which are column names like - index, user etc or just add gravis to the name in order to avoid mistakes

    user -> user

Execute the query and use the result. In the final result you will have the table name, the schema and the column name. This can be very useful if you want to find inconsistent or corrupted data:

Count values Schema Table Column
20 test aboys id
20 test aboys name
12 test aboys relation
5 test agirls id
5 test agirls name

MySQL count null and not null values for several columns

The same trick apply for MySQL(you can use this solution also for Oracle):

SELECT 
	COUNT(colx) x_not_null, -- count colx not null values
	COUNT(coly) y_not_null, -- count coly not null values
	COUNT(*) - COUNT(colx) x_null, -- count colx null values
	COUNT(*) - COUNT(coly) y_null, -- count coly null values
	COUNT(CASE WHEN colx IS NOT NULL AND coly IS NOT NULL THEN 1 END) x_y_not_null, -- count colx and coly not null values
	COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null-- count colx and coly  null values
FROM table

The result:

x_not_null y_not_null x_null y_null x_y_not_null x_y_null
12 5 5 11 1 1

Oracle SQL select count null values per column

This examples are tested with Oracle. They are using standard SQL so they will work also on MySQL or any other DB which is following SQL standards.

Count by multiple selects

  • for table named person with owner powner generate SQL query which counts all values(not null) per column.

  • Run this SQL (first replace POWNER and PERSON with your names)

SELECT 'select count('
     || column_name
     || '),     ''' ||     
     column_name || ''' from POWNER.PERSON union' AS table_name
   FROM all_tab_cols
   WHERE table_name = 'PERSON'
   AND owner        = 'POWNER'
  • Copy the result and paste it in new Query tab

  • Delete the last union and run the result query

select count(id), 'id' from powner.person union
select count(version), 'version' from powner.person 
  • Result
Count(id) id
10 id
10 version
7 name

So the column name only 7 rows has value and the rest 3 are nulls

Count by single select query

You can do the same as above with a single query by using LISTAGG oracle function.

  • Change POWNER and PERSON with your owner and table name.
SELECT 'select '
 || LISTAGG( column_name, ' ' ) WITHIN GROUP (
ORDER BY table_name)
 || '''PERSON'' from POWNER.PERSON' "names"
FROM
 (SELECT *
 FROM
   (SELECT 'count('
     || column_name
     || '), '         AS column_name,
     'PERSON' AS table_name
   FROM all_tab_cols
   WHERE table_name = 'PERSON'
   AND owner        = 'POWNER'
   )
 )
GROUP BY table_name
  • Get the result and run it in new tab query:

This query is the result of the previous step. It will contain all columns for your table. The final result would be count of all non null values per column.

SELECT 
COUNT(ID), 
COUNT(VERSION), 
COUNT(NAME), 
 'PERSON'
FROM POWNER.PERSON
  • final result(as you can notice the result is transposed - not vertically but horizontally)
Count(id) Count(version) Count(name)
10 10 7
  • So if you want to transpose the result you can do it by using pivot function:
SELECT 
id, VERSION,name
FROM powner.person
 UNPIVOT (Count(id) FOR id IN (VERSION AS 'version'
,name AS 'name')
)
)

Oracle count null and not null values for several columns

You can use count in order to get information about the null and not null values in your tables. In this example you are counting the null and not null values for a column. You have also sum of null values of several columns. For example:

  • COUNT(colx) - this will count all non null values for column colx in Oracle(but the same will apply for MySQL or anu other SQL standard DB.
  • COUNT(`*) - COUNT(colx) - using this will return the number of null values in column colx
  • COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null - return null values in more columns. this can be used when you have related information like price and quantity:
SELECT 
	COUNT(colx) x_not_null, -- count colx not null values
	COUNT(coly) y_not_null, -- count coly not null values
	COUNT(*) - COUNT(colx) x_null, -- count colx null values
	COUNT(*) - COUNT(coly) y_null, -- count coly null values
	COUNT(CASE WHEN colx IS NOT NULL AND coly IS NOT NULL THEN 1 END) x_y_not_null, -- count colx and coly not null values
	COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null-- count colx and coly  null values
FROM table

Previous post on SQL:

If you need quick reference on SQL selects and joins:
Frequent SQL commands select

If you want to need to update or delete data/tables:
Frequent SQL commands DML and DDL

Related Article