Date:         Mon, 25 Aug 2008 08:31:52 -0700
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: SQL question
Comments: To: karma <dorjetarap@GOOGLEMAIL.COM>
In-Reply-To:  <7d6ebc8c-2382-441e-b383-e17fe87c07d4@k13g2000hse.googlegroups.com>
Content-Type: text/plain; charset="ISO-8859-1"

On Mon, 25 Aug 2008 03:34:55 -0700, "karma" <dorjetarap@GOOGLEMAIL.COM>
said:
> On Aug 25, 8:35 am, hawks_cher...@YAHOO.CO.IN (cherish k) wrote:
> > Hi All,
> >
> > Can somebody explain if there is any difference the two SQL codes.
> >
> > select * from TABLE_A where IC not in (select IC from TABLE_B)
> >
> > select * from TABLE_A a where a.IC not in (select b.IC from TABLE_B where a.IC = b.IC)
> >
> > I expected both the codes to be same. But output from the first table gave 0 records whereas from the second code few records came up and the second code is correct.
> >
> > I ran the code in Oracle SQL. In SAS both codes performe the same (i tested on a random sample), does it work differently in SQL?
> >
> > Cherish
> >
> > ---------------------------------
> >  Connect with friends all over the world.  Get Yahoo! India Messenger.
>
> Hi Cherish, the 'a' after the table name is an alias - the same as 'as
> a'. It is used to differentiate between variable with the same name
> from different tables. If the variable IC exists on both TABLE_A and
> TABLE_B the first code should produce an error as the table IC is from
> is not specified -consequently no records will be returned. When in
> doubt use aliases.

The first example is just a regular subquery, not a correlated subquery,
so a table alias is not needed in (select IC from TABLE_B).  This is
also known as an in-line view.

See the example under "Query Expressions (Subqueries)" at
<http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a002473695.htm>.

I agree with your comment "When in doubt use aliases."

I would specify the alias in a more explicit way than the original
poster.  Instead of " select * from TABLE_A a" I would code "select *
from TABLE_A as a".  The keyword "as" might not be accepted in all SQL
implementations.




--
Jack Hamilton
Sacramento, California
jfh@alumni.stanford.org  <== Use this, not jfh@stanfordalumni.org