Natural join

Question

Q1. Suppose in an SQL query I have natural join as :        from R1 natural join R2 natural join R3 natural join R4

then how does the natural join works? According to me, First R1 joins with R2 matching common attributes value between R1 and R2 then the result of R1, R2 joins with R3 matching common attributes between R1R2 and R3 and so on. Am I correct?

Q2. if no attribute matches in natural join:   from R1 natural join R2

will the result be an empty relation?

Q3.  page No. 73 Of Korth Book

Relation schemas of three relations are as follows

       course(course_id, title, dept_name, credits)
       instructor(ID, name, dept_name, salary)
       teaches(ID, course_id, sec_id, semester, year)

Query is “List the names of instructors along with the the titles of courses that they teach.”

So the SQL query used is:       select name, title
                                             from instructor natural join teaches, course
                                             where teaches.course_id= course.course_id;

But at the bottom of the page, it is given

 " it is not possible to use attribute names containing the original relation names, for instance, instructor.name or teaches.course_id, to refer to attributes in the natural join result; we can, however, use attribute names such as name and course_id, without the relation names "  

but teaches.course_id is used in the query. WHY? It's not allowed.

Comments

@Anirudh

@habibkhan

Answer 1

Q-1

Yes the method is same as you have written .

Q-2
A cartesian product of two tables will be returned.This is because when we perform any JOIN operation on two tables a cartesian product of those tables is performed and then based on any select condition in WHERE clause the resultant rows are returned.But here as there are no common columns the process stops after cartesian product.

Comments

What about Question 3

Answer 2

Q 3rd:

ummm……. because after a JOIN is done consisting of two tables , u can can consider the attributes now to be a part of the newly formed table instead of their ancestors actually which they were part of.

And so can write just name, title instead of ancestor.table_name meanwhile……

YES teaches.course_id and course.course_id (ancestor.table_name) is present in the Query bcz while writing the query , the NATURAL JOIN hasn’t been performed yet……

First this line executes:  1.“instructor natural join teaches, course”…... , then this line:   2.“where teaches.course_id= course.course_id”………. and then NATURAL JOIN happens………. nd then the line with SELECT statement:  3.“ select name, title” executes………And it is VALID.

 Bcz in PREV. step onli (after 2. nd before 3.) NATURAL JOIN has been done nd so just table_name without ancestor.table_name (in 3.) can be written here………...btw i m NOOB nd may be wrong also bt if it helps thenn……...