Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Natural Join with ON clause
thiyagusham
post Apr 22 2012, 09:04 AM
Post #1


Advanced Member
***

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939



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
Go to the top of the page
 
+Quote Post
RAKESH S
post Apr 22 2012, 01:28 PM
Post #2


Newbie
*

Group: Members
Posts: 4
Joined: 10-April 12
Member No.: 47,064



QUOTE (thiyagusham @ Apr 22 2012, 10:04 AM) *
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


Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 19th April 2014 - 05:34 PM