Date: Mon, 25 Aug 2008 08:31:52 -0700
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: SQL question
Content-Type: text/plain; charset="ISO-8859-1"
On Mon, 25 Aug 2008 03:34:55 -0700, "karma" <dorjetarap@GOOGLEMAIL.COM>
> 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
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
firstname.lastname@example.org <== Use this, not email@example.com