SQL Query multiple table with same column

Question

I have 15 tables and they all have the columns CreationDate and LastModifiedDate is there a way to query all the tables and look for null values in the tables.

This is what I am thinking I have to do but was wondering if there is a more efficient way or easier way to do the following

Select COUNT(*) FROM Table1, Table2, Table3
WHERE table1.CreationDate IS NULL
      OR table1.LastModifiedDate IS NULL
      OR table2.CreationDate IS NULL
      OR table2.LastModifiedDate IS NULL
      OR table3.CreationDate IS NULL
      OR table3.LastModifiedDate IS NULL

D'Arcy Rittich · Accepted Answer · 2012-08-09 15:45:09Z

3

select count(*)
from (
    select CreationDate, LastModifiedDate from Table1
    union all
    select CreationDate, LastModifiedDate from Table2
    union all
    select CreationDate, LastModifiedDate from Table3
) a 
where CreationDate is null or LastModifiedDate is null

answered Aug 9, 2012 at 15:45

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Perrenoud Over a year ago

Very clever - +1 on that one!

Jamie Babineau Over a year ago

Would there be an easy way to update those times that are null to GETDATE()?

D'Arcy Rittich Over a year ago

A standard update statement will do this. Ask as a new question if you are unsure how.

Gordon Linoff · Accepted Answer · 2012-08-09 15:58:48Z

You original query does not do what you expect. It does a cross join among all the rows that have missing values. This can result in a ton of data (if all tables have multiple rows) or none at all (if one has no missing rows).

I assume that you want to know the table name where things are missing:

select tablename, count(*),
       sum(case when CreationDate is null then 1 else 0 end) as MissingCreationDate,
       sum(case when LastModifiedDate is null then 1 else 0 end) as MissingLastModifiedDate
from ((select 'table1' as tablename,  CreationDate, LastModifiedDate
       from Table1 
      ) union all
      (select 'table2' as tablename, CreationDate, LastModifiedDate
       from Table2
      ) union all
      . . .
      (select 'table15', CreationDate, LastModifiedDate
       from Table3
      ) 
     ) t
where CreationDate is null or LastModifiedDate is null
group by tablename

Collectives™ on Stack Overflow

SQL Query multiple table with same column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related