Re: Problem with Retrieving Data from Two Tables

From: Christian Maslen <cmaslen_at_nospam.bigpond.net.au>
Date: Sun, 27 Jan 2002 06:42:05 GMT
Message-ID: <1jN48.14426$Ni2.74612_at_news-server.bigpond.net.au>


Hi Guido,

Is the outer join style quicker than exists in Oracle? I'd have gone with the following...

SELECT      *
 FROM        table1 ue,
 WHERE     not exists (select    *
                                    from      table2
                                    where    ue.user_id = table2.user_id)

but being new to Oracle, I don't know if this works as quickly.

Christian.

PS. What would happen if a row in table2 had a NULL user_id?

"Guido" <NoSpam_at_MyAccount.com> wrote in message news:1011689644.957740_at_news.thyssen.com...
> Hi HOque,
>
> (would be better to user your real name),
> maybe you want something like this:
>
> SELECT ue.*
> FROM table1 ue, table2
> WHERE ue.user_id = table2.user_id(+)
> AND table2.user_id IS NULL;
>
> Hth,
> Guido
>
>
> "Leader" <sohelcsc_at_yahoo.com> schrieb im Newsbeitrag
> news:b1a93c73.0201212347.71a4b084_at_posting.google.com...
> > HI All,
> > I am facing a problem what i am describing below.
> >
> > Suppose I have one table TAble1 where the fields are user_ID, email.
> > and i have another table Table2 where the fiels are also user_ID ,
 email.
> > Table2's User ID is foreign key from Table1.
> > Now i want the User_ID and Email from Table1 which are not exist in
 Table2.
> > I have one sql statement which uses NOT IN.
> > select ue.user_ID, ue.email
> > from Table1 ue
> > where ue.User_ID NOT IN
> > (select User_ID
> > from Table2) order by user_ID
> >
> >
> > but i don't want to use it. I want
> > any other statement without NOT IN. Can anyone help me please?
> >
> > Thanks
> > HOque
>
>
Received on Sun Jan 27 2002 - 07:42:05 CET

Original text of this message