Simple SQL Puzzle

From: Chris Brady <chrisjbrady_at_yahoo.com>
Date: 29 Oct 2001 03:14:37 -0800
Message-ID: <51604466.0110290314.226d2546@posting.google.com>

Please can someone explain this? Its the same table compared with itself. The first case I can understand but why is the second case giving the same total? I would have thought that it would be zero.

SQL> select count(distinct(i.myfield)) from temp_data i, temp_data w

where i.myfield = w.myfield;

COUNT(DISTINCT(I.MYFIELD))

                     47548

SQL> select count(distinct(i.myfield)) from temp_data i, temp_data w
     where i.myfield <> w.myfield;

COUNT(DISTINCT(I.MYFIELD))

Many thanks - Chris B. Received on Mon Oct 29 2001 - 05:14:37 CST