Puzzled by this SQL?

From: Adrian Harrison <harryreguk_at_googlemail.com>
Date: Fri, 12 Aug 2011 03:00:35 -0700 (PDT)
Message-ID: <86d7eaa0-661f-408e-8bf4-03c320e741f5_at_en1g2000vbb.googlegroups.com>

"asset" table contains 163 rows
"asset_attr_value" table contains 200 rows (but only 19 rows match
where clause below)

This SQL using EXISTS returns 163 rows -

SELECT asset_id
FROM asset
WHERE

       EXISTS (SELECT asset_id
                          FROM   asset_attr_value av
                          WHERE  ( av.clasfn_schm_key = 'base'
                                   AND av.attr_key =
'test_attr_base_1'
                                   AND av.value_text = 'a1' )
                                 AND av.record_deleted = 'N')

ORDER BY asset_id

But this one using IN returns 19 -

SELECT asset_id
FROM asset
WHERE

       asset_id IN (SELECT asset_id
                          FROM   asset_attr_value av
                          WHERE  ( av.clasfn_schm_key = 'base'
                                   AND av.attr_key =
'test_attr_base_1'
                                   AND av.value_text = 'a1' )
                                 AND av.record_deleted = 'N')

ORDER BY asset_id

The IN is correct but why does the 1st return all, surely this should return 19 also?

thanks in advance Received on Fri Aug 12 2011 - 05:00:35 CDT