Re: Newbie question: select row in a table that not exist in other table.

jose_luis_fdez_diaz_news_at_yahoo.es wrote:
> Hi,
>
>
> 1)
>
> select GPRS_CO_MSISDN
> from GPRS_SERVICIO_GPRS
> where NOT EXISTS (select 1 from SAPN_SERVICIO_APN
> where SAPN_CO_MSISDN =
> GPRS_SERVICIO_GPRS.GPRS_CO_MSISDN
> )
> group by GPRS_CO_MSISDN;
>
>
> 2)
>
> select g.GPRS_CO_MSISDN from GPRS_SERVICIO_GPRS g, SAPN_SERVICIO_APN a
> where NOT (g.GPRS_CO_MSISDN = a.SAPN_CO_MSISDN)
> group by g.GPRS_CO_MSISDN;
>
>
> Two questions:
>
> Is the result set the same in both queries ?
>
> Why query 1) is faster than query 2 ?
>
>
> Thanks in advance,
> Jose Luis.

The resultset is not the same.
1 is equivalent with an outer join
select g.GPRS_CO_MSISDN from GPRS_SERVICIO_GPRS g, SAPN_SERVICIO_APN a where g.GPRS_CO_MSISDN = a.SAPN_CO_MSISDN(+) and a.sapn_co_msisdn is null
group by g.GPRS_CO_MSISDN;
Your solution doesn't work, ad a.sapn_co_msisdn is either identical to g.gprs_co_msisdn or NULL. As NULL doesn't equal anything, the records you want are suppressed.
2 Using Cost Based Optimizer in a properly set up system, there should be no difference. The first statement, if the table is properly indexed, simply looks up 1 single appropiate record from the affected index, it doesn't read the table at all (this is why you are using the dummy 1), the second statement definitely reads the table.

Hth

-- 
Sybrand Bakker,
Senior Oracle DBA