Natural Join in related table

Question

I have to use only to use natural join it is not working in sql server,,, i have to select EmpName,EmpDOB and EMPDOB from employee table and just DEPTID from department table..please help

SELECT     DEPARTMENT.DEPTID, EMPLOYEE.EmpID, EMPLOYEE.EMPName, EMPLOYEE.EMPDOB
FROM         DEPARTMENT NATURAL JOIN
                      EMPLOYEE ON DEPARTMENT.DEPTID = EMPLOYEE.DEPTID

Hint: a natural join doesn't need to specify the columns used in the join. — Gordon Linoff, Jun 19 '13 at 18:31
Why use a Natural Join? Seems like an Inner Join would do just as well and is a normal kind of join. — Brad, Jun 19 '13 at 18:35
i am doing homework so i must use natural join..i am stuck i have tried alot — user2393171, Jun 19 '13 at 18:36
`NATURAL JOIN` won't work with an `ON` statement. No need to specify what fields are being joined, `NATURAL` takes care of that for you. — Kris Gruttemeyer, Jun 19 '13 at 18:38
please send this to your teacher http://stackoverflow.com/questions/4826613/natural-join-in-sql-server — Alex Gordon, Jun 19 '13 at 18:42

score 3 · Answer 1 · edited Jun 19 '13 at 19:05

3

If you must use a NATURAL JOIN then try this:

SELECT D.DEPTID, E.EmpID, E.EMPName, E.EMPDOB
FROM DEPARTMENT D NATURAL JOIN EMPLOYEE E

As long as the column names DEPTID are the SAME on both tables. NATURAL JOIN Doesn't need to specify what fields are joined, it figures it out on it's own.

Here's a great reference on the NATURAL JOIN: http://www.w3resource.com/sql/joins/natural-join.php

The Natural Join won't work in SQL-Server though, only in MySQL.

edited Jun 19 '13 at 19:05

ypercubeᵀᴹ

113,259
19
174
235

answered Jun 19 '13 at 18:33

Kris Gruttemeyer

872
7
19

i am doing homework..i am supposed to use Natural join ..please helpp – user2393171 Jun 19 '13 at 18:34
What version of SQL are you using? – Kris Gruttemeyer Jun 19 '13 at 18:50
SQL SERVER two thousand eight – user2393171 Jun 19 '13 at 18:53

score 3 · Answer 2 · answered Jun 19 '13 at 19:00

3

SQL-Server has not implemented NATURAL JOIN.

If you want to experiment with its use, try Oracle or Postgres or MySQL. You can also use SQLfiddle.com if you are not allowed to install them.

answered Jun 19 '13 at 19:00

ypercubeᵀᴹ

113,259
19
174
235

score 0 · Accepted Answer · answered Jun 19 '13 at 18:40

0

When you use the '=' sign it is just a normal equi-join (explicit) while a natural join the predicates are figured out by the query engine (implicit). http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join

answered Jun 19 '13 at 18:40

Mike Cheel

12,626
10
72
101

Yeah, that would work just as easily, but it looks like, for the purposes of the assignment, he HAS to use a natural join. – Kris Gruttemeyer Jun 19 '13 at 18:41
i have tried this also and it is giving Syntax error near department SELECT * FROM employee NATURAL JOIN department; – user2393171 Jun 19 '13 at 18:51

Natural Join in related table

3 Answers3