Puzzling SQL problem with nulls and dates

From: Al Chase <alchase_at_airmail.net>
Date: 1997/01/30
Message-ID: <32f0eabd.15032766@news.airmail.net>#1/1

Columns A, B and C are all date types
In the queries below, query 1) counted where any of the three columns were not null.
Queries 2,3,4 counted where any individual column A,B,C were null. It seems that then subtracting the duplications of non-nullness betweeen columns
from the sum of queries 2,3,4 should get the same result as query 1). But, no. Why not? The same numbers are gotten if the distinct is dropped or if
'distinct id' is replaced by *. These were spooled from a table at work and the
column and snapshot names have been changed for clarity.

1696               A is not null                   Query 2
 344               or B is not null                Query 3 
1404               or C is not null                Query 4


- 42               if there is a duplication between A and B but not C




-261               if there is a duplication between A and C but not B




- 85               if there is a duplication between B and C but not A




- 30               if there is a 'triplication' between A, B, and C

3026

This does not match Query 1) immediately below.

What gives? Thanks in advance.

Al
alchase_at_airmail.net

SQL>--                    1) count if any column is not null

SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_A is not null
  5 or Column_B is not null
  6 or Column_C is not null;

COUNT(DISTINCTID)

2996

SQL>--                  2)  count if Column_A is not null

SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_A is not null;

COUNT(DISTINCTID)

1696

SQL>--                   3) count if Column_B is not null

SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_B is not null;

COUNT(DISTINCTID)

344

SQL>--                   4) count if Column_C is not null

SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_C is not null;

COUNT(DISTINCTID)

1404

SQL>--                 5)  count if Column  A and B are not null but

Column C is
SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_A is not null
  5 and Column_B is not null
  6 and Column_C is null;

COUNT(DISTINCTID)

SQL>--                 6)  count if Column  A and C are not null but

Column B is
SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_A is not null
  5 and Column_C is not null
  6 and Column_B is null;

COUNT(DISTINCTID)

261

SQL>--                 7)  count if Column  B and C are not null but

Column A is
SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_B is not null
  5 and Column_C is not null
  6 and Column_A is null;

COUNT(DISTINCTID)

SQL>--                 8)  count if Column  A, B and C are all not

null
SQL>
SQL> SELECT count(distinct id)
  2 FROM the_snapshot
  3 where
  4 Column_B is not null
  5 and Column_C is not null
  6 and Column_A is not null;

COUNT(DISTINCTID)

SQL>
SQL> spool off; Received on Thu Jan 30 1997 - 00:00:00 CST