In this article we are going to present several ways of counting null and not null values per table(s) by union or single query.

Counting null / not null single table single query

You can check all tables in schema for null and/or not null values by:

  • schema: test
  • table: fiscal

Step 1 Prepare select collecting values per table

SELECT CONCAT('select ', GROUP_CONCAT(cols SEPARATOR ', '), ' from ', table_name) AS idList
FROM(
SELECT CONCAT('count(',column_name, ')') AS cols, table_name
FROM information_schema.columns where table_name = 'fiscal') AS cols

Step 2 Counting null and not null values

select count(id), count(year), count(flag1), count(flag2), count(flag3), count(result) from fiscal

result:

count(id) count(year) count(flag1) count(flag2) count(flag3) count(result)
4 4 4 2 3 4

Counting null / not null values in MySQL 1 for one table with union

Step 1 Create query to prepare selects for counting null and not null

We are going to perform select against : information_schema and collect required information. In this example we are working with:

  • schema: test
  • table: fiscal
SELECT CONCAT('select count(',column_name,'),     ''', column_name, ''' from ', table_name, ' union')
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 'fiscal'
ORDER BY table_name,ordinal_position;

result is union select as follows(you will have an extra union at the end - just remove it - only the last one):

select count(id),     'id' from fiscal union
select count(year),     'year' from fiscal union
select count(flag1),     'flag1' from fiscal union
select count(flag2),     'flag2' from fiscal union
select count(flag3),     'flag3' from fiscal union
select count(result),     'result' from fiscal --union

Step 2 Counting null and not null

We are going to use count which is working in such a way that allow us to collect information for not null and null columns in a table. This is the sql:

select count(id),     'id' from fiscal union
select count(year),     'year' from fiscal union
select count(flag1),     'flag1' from fiscal union
select count(flag2),     'flag2' from fiscal union
select count(flag3),     'flag3' from fiscal union
select count(result),     'result' from fiscal

the result is:

4 id
4 year
4 flag1
2 flag2
3 flag3
4 result

Which is exactly the expected result:

  • id - no null values
  • year - no null values
  • flag2 - 2 not null values
  • flag3 1 null value..

Counting null / not null many tables

You can check all tables in schema for null and/or not null values by:

  • schema: test
  • table: all
SELECT CONCAT('select count(',column_name,'),     ''', column_name, ''' from test.', table_name, ' union')
FROM information_schema.columns
WHERE table_schema = 'test'
ORDER BY table_name,ordinal_position;
  • schema: test
  • table: all
  • Including table names in the result:
SELECT CONCAT('select ''', table_name, ''', ''', column_name, ''', count(',column_name,')     ',' from test.', table_name, ' union')
FROM information_schema.columns
WHERE table_schema = 'test'
ORDER BY table_name,ordinal_position;

result:

select 'fiscal', 'id', count(id)      from test.fiscal union
select 'fiscal', 'year', count(year)      from test.fiscal union
select 'fiscal', 'flag1', count(flag1)      from test.fiscal union
select 'fiscal', 'flag2', count(flag2)      from test.fiscal union
select 'fiscal', 'flag3', count(flag3)      from test.fiscal union
select 'fiscal', 'result', count(result)      from test.fiscal union
select 'table1', 'key', count(key)      from test.table1 union
select 'table1', 'parent', count(parent)      from test.table1 union
select 'table2', 'key', count(key)      from test.table2

output:

fiscal id count(id)
fiscal id 4
fiscal year 4
fiscal flag1 4
fiscal flag2 2
fiscal flag3 3
fiscal result 4
table1 key 0
table1 parent 0
table2 key 8

Database schema

This is the table and data used for this example:

CREATE TABLE fiscal
	(`id` int, `year` int(4), `flag1` varchar(5), `flag2` varchar(3), `flag3` varchar(3), `result` int(2))
;

INSERT INTO fiscal
	(`id`, `year`, `flag1`, `flag2`, `flag3`, `result`)
VALUES
	(1, '2015', '10.1', 'B', NULL, 1),
	(1, '2016', '10.2', NULL, 'G', 1),
	(1, '2017', '10.3', NULL, 'H', 2),
	(1, '2018', '11', 'C', 'y', 3)
;
1 2015 10.1 B 1
1 2016 10.2 G 1
1 2017 10.3 H 2
1 2018 11 C y 3

Which way you are going to use depends on your needs and preferences. You may check also how to do the same operation in Oracle:

Oracle count null and not null values in column