Re: Three valued logic (again)

In article <331D9EA3.4DAB@itc.nrcs.usda.gov>, Jason Restad <jasr@itc.nrcs.usda.gov> writes >However, I am still am left with a puzzle related to NULLs. >Here is a query to demonstrate: > >set explain on; >CREATE TABLE tbl ( col int);> >INSERT INTO tbl VALUES (1); >INSERT INTO tbl VALUES (''); Since integers cannot contain '' this will become a null. > >SELECT COUNT(*) FROM tbl WHERE col IS NULL; returns 1 Correct since 1 row in the table contains a null. >SELECT COUNT(col) FROM tbl WHERE col IS NULL; What does COUNT(col1) really mean? I have never seen a description of this and I do not know what it means. COUNT(*) gives a count of the rows (is strict SQL terms tuples) which matches the query. I suspect that since sql is set based select count(col) from table where col1 is NULL means a) vertically split the table i.e. take each tuple in this table and convert to a tuple consisting only of the column. i.e. set A which is tuples {1} and {NULL}. i.e. set A = { (1) (NULL) } b) find which tuples are in in the results set from the query The results set from the query is set B which consists of the tuple (NULL) i.e. set B = { (NULL) } b) Count how many tuples are in the set A interset B i.e. A n B. i.e. { (1) (NULL) } n { (NULL) } since (NULL) = (NULL) is FALSE. The answer is 0. whereas select count(*) from table where col1 is NULL means b) find which tuples are in in the results set from the query The results set from the query is set B which consists of the tuple (NULL) i.e. set B = { (NULL) } d) Count how many tuples are in set B. The answer is 1. PS Got the 0 and 1 the right way around. Phew!. PPS All this is my theories based on the fact that SQL is really all about sets and set theory...Remember A n B...A u B etc?.. i.e. the only thing I am really sure about is the fact that SQL is set based since that is what my university course thought me, >DROP TABLE tbl;> >This returns: > (count(*)) > 1 > > (count) > 0 > > >Why do I not get a "1" for BOTH? >I ran this same query with a > "SET EXPLAIN ON;" >at the beginning. Both SELECTs seem to be handled the same >yet if I specify a Column name in the COUNT() it seems at >though NULLs DON'T COUNT. -- David Williams