In this article we will post two ways of counting null and not null values per table(s) by union or single query with Oracle 11g.

Counting null / not null single table single query with LISTAGG

You can check all tables in Oracle schema for null and/or not null values by single query. In creating this query we are going to list LISTAGG function which is extremely helpful for such situations. It's used for pivot/unpivot data. The example use this schema and table:

  • schema: myowner
  • table: mytable

Step 1 Prepare select collecting values per table

SELECT 'select '
  || LISTAGG( column_name, ' ' ) WITHIN GROUP (
ORDER BY table_name)
  || '''mytable'' from myowner.mytable' "names"
FROM
  (SELECT *
  FROM
    (SELECT 'count('
      || column_name
      || '), '         AS column_name,
      'MBCPF_COVERAGE' AS table_name
    FROM all_tab_cols
    WHERE table_name = 'mytable'
    AND owner        = 'myowner'
    )
  )
GROUP BY table_name;

result is the select of step 2

Step 2 Counting null and not null values

SELECT COUNT(ID),
  COUNT(STATUS),
  COUNT(ODATE),
  COUNT(SDATE),
  'mytable'
FROM myowner.mytable

result:

count(ID) count(STATUS) count(ODATE) count(SDATE)
4 4 4 2

Counting null / not null by union in Oracle

We can do similar operation with a small difference - the output will be in rows and not columns like previous one:

  • schema: myowner
  • table: mytable

Step 1 Prepare select collecting values per table

SELECT 'select count('
  || column_name
  || '),     '''
  || column_name
  || ''' from myowner.mytable union' AS table_name
FROM all_tab_cols
WHERE table_name = 'mytable'
AND owner        = 'myowner';

result is the select of step 2

Step 2 Counting null and not null values

select count(ID),     'ID' from myowner.mytable union
select count(STATUS),     'STATUS' from myowner.mytable union
select count(ODATE),     'ODATE' from myowner.mytable union
select count(SDATE),     'SDATE' from myowner.mytable;

result:

4 ID
4 STATUS
4 ODATE
2 SDATE

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

MySQL 5.7 count null and not null values in column