SQL Expression for Referential Integrity

[Staceyd]

I have been executing the following SQL expression to check for referential integrity. However, I am confused about the results I get back.

select count(*)
from
(
select b.shop_order,
b.so_time_stamp
from inventory_history b
where not exists
(select *
from shop_order s
where ih.shop_order_nbr = b.shop_order
and ih.so_time_stamp = b.so_time_stamp)
group by b.shop_order,
b.so_time_stamp
) a

The results of this query indicate that the number of shop orders (so_timestamp, shop_order) in Invt. History (child) that are not in the Shop Order table (parent) = 307,222.

However, a query of Invt. History indicates that there are 16,491,550 records with so_timestamps = null

select count(*) from inventory_history
where so_timestamp is null

And a query of Shop Order indicates that there are no records with the so_timestamp = null.

select count(*) from shop_order
where so_timestamp is null.

Why wouldn't the referential integrity analysis query above return at least 16,491,550?

 

[swampBoogie]

What is ih? There is no such correlation name or table name in the query.

 

[Staceyd]

Whoops! ih should be s in this query. Sorry!!!

 

[dnoeth]

I'd expect the larger number, too.
What DBMS do you use?

Dieter

 

[Staceyd]

Teradata V2R5