CODEWITHSUNDEEP
sqlsql-servernatural-join
Hiep
Hiep

Reputation: 107

Cannot run a natural join

I have two tables:

I want to retrieve title and dept_name using a natural join on the two tables, but it returns an error:

incorrect syntax near ';'

My code:

select title, dept_name
from takes 
natural join course;

What is wrong with my code?

PS. My textbook does not mention using the on keyword. Besides that, it also mentions to use the operator using to specify the common attributes, but this doesn't help either.

Upvotes: 0

Views: 4223

Answers (3)

Deepshikha
Deepshikha

Reputation: 10274

Well standard SQL supports a concept called natural join, which represents an inner join based on a match between columns with the same name in both sides. For example, T1 NATURAL JOIN T2 joins the rows between T1 and T2 based on a match between the columns with the same names in both sides. T-SQL being a dialect of SQL, doesn’t have an implementation of a natural join, as of SQL Server 2012. So in your case as takes and course has a common column Course_ID , equivalent representation in T-SQL will be:

select C.title, C.dept_name
from takes T
INNER JOIN course C on C.Course_ID = T.Course_ID;

Upvotes: 1

user330315
user330315

Reputation:

Besides that, it also mentions to use the operator using to specify the common attributes, but this doesn't help either.

You either mis-read the textbook or that textbook is plain wrong. natural join does not require any specification on which column to use.

The USING attribute is used for a "regular" join:

select c.title, c.dept_name
from takes t
  join course c using (Course_ID);

join is equivalent to inner join. The keyword inner is optional.

join course using (Course_ID) is equivalent to join course c on t.course_id = c.course_id

Upvotes: 0

Mukund
Mukund

Reputation: 1689

Sqlserver does not support natural join. Instead of that you can use

INNER JOIN

select c.title, c.dept_name
from takes t
inner join course c on t.Course_ID = c.Course_ID;

Upvotes: 0

Related Questions