Re: ANSI joins

On Dec 3, 12:13 am, "BigLearner" <Small.Lear..._at_gmail.com> wrote:
> Hai everyone,
>
> I have a doubt on joins.
>
> select e.empno, e.ename, d.loc
> from emp e, dept d
> where e.deptno = d.deptno
> and substr(e.ename,1,1) = 'S';
>
> How can I rewrite it in ANSI compliant equivalent statement usable on
> the Oracle database?
>
> select empno, ename, loc
> from emp join dept
> on emp.deptno = dept.deptno
> where substr(emp.ename,1,1) = 'S';
>
> or
>
> select empno, ename, loc
> from emp join dept
> on emp.deptno = dept.deptno
> and substr(emp.ename,1,1) = 'S';
>
> Which one of them is right, using WHERE or AND for the last condition?
>
> Do guide me.
>
> Thanks.
>
> BigLearner

Actually your first SQL statement is fully ANSI compliant. The newer ANSI-92 join syntax is in addition to the earlier syntax. It is legal to code "and" conditiona in the ON clause or in the WHERE clause. With only one condition I am not sure it matters but with multiple conditions I would hazard to say the convention is to place them in the WHERE clause.

The idea behind the new syntax was to make the join conditons obvious so you should place the filter conditions in the WHERE clause. Personally I stick to the older syntax.

IMHO -- Mark D Powell -- Received on Sun Dec 03 2006 - 13:42:01 CST