Re: Puzzled by this SQL?

From: Adrian Harrison <harryreguk_at_googlemail.com>
Date: Fri, 12 Aug 2011 04:05:42 -0700 (PDT)
Message-ID: <e3f2b6e7-1138-46ec-b11f-08438fbcabfa_at_t6g2000yqd.googlegroups.com>

On Aug 12, 11:19 am, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> Adrian Harrison wrote:
> > "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.- Hide quoted text -
>
> - Show quoted text -

ah ha!

SELECT a.asset_id
FROM asset a
WHERE

       EXISTS (SELECT av.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' and

a.asset_id = av.asset_id)
ORDER BY a.asset_id

thanks for that, is there any advantage is using IN over EXISTS in this case? Received on Fri Aug 12 2011 - 06:05:42 CDT