Re: Puzzled by this SQL?

> "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?

Think about what you're missing; how does the outer query know when there's a matching asset_id in the sub-query?

-- 
Preston.