Natural Join with ON clause Options

[thiyagusham]

CODE

SQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800


SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)

CODE

SQL> select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;

on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> select x.empid,x.name,x.salary,y.dept_name from em1 x JOIN dept y
2 on x.empid=y.empid;

EMPID NAME SALARY DEPT_NAME
---------- ---------- ---------- ---------------
1060 sam 4000 INFO TECH
1061 rose 3700 BIO INFO

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this



SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended



-------------------

Thanks and Regards
Thiyagusham.G

[RAKESH S]

CODE

SQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800


SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)
select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;

CODE

SQL>

on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL> select x.empid,x.name,x.salary,y.dept_name from em1 x JOIN dept y
2 on x.empid=y.empid;

EMPID NAME SALARY DEPT_NAME
---------- ---------- ---------- ---------------
1060 sam 4000 INFO TECH
1061 rose 3700 BIO INFO

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this



SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended



-------------------

Thanks and Regards
Thiyagusham.G

Hi ,


when you are using natural join you cannot use on clause

Try

select empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y

It will check common column in both table and perform join on the same
ur eg. has empid as common column so in select empid should be used instead of x.empid