I am studying the book Database System Concepts 6th ed. which uses the example of a university database. The schema of this database is as follows. The bold represent the primary keys:
department(dept_name, building, budget) course(course_id, title, dept_name, credits) instructor(ID, name, dept_name, salary) section(course_id, sec_id, semester, year, building, room number, time_slot_id) teaches(ID, course_id, sec_id, semester, year) prereq(course_id, prereq_id)
In the book the following query is stated:
"List the names of instructors along with the the titles of courses that they teach".
The answer given by the book is:
select name, title
from instructor natural join teaches, course
where teaches.course id = course.course id;
Furthermore, the writer goes:
"In contrast the following SQL query does not compute the same result:
select name, title
from instructor natural join teaches natural join course;
To see why, note that the natural join of instructor and teaches contains the attributes (ID, name, dept name, salary, course id, sec id), while the course relation contains the attributes (course id, title, dept name, credits). As a result, the natural join of these two would require that the dept name attribute values from the two inputs be the same, in addition to requiring that the course id values be the same. This query would then omit all (instructor name, course title) pairs where the instructor teaches a course in a department other than the instructor’s own department. The previous query, on the other hand, correctly outputs such pairs. "
Now I tried both queries to see myself the difference.
mysql> select * from instructor;
+-------+------------+------------+----------+
| ID | name | dept_name | salary |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu | Finance | 90000.00 |
| 15151 | Mozart | Music | 40000.00 |
| 22222 | Einstein | Physics | 95000.00 |
| 32343 | El Said | History | 60000.00 |
| 33456 | Gold | Physics | 87000.00 |
| 45565 | Katz | Comp. Sci. | 75000.00 |
| 58583 | Califieri | History | 62000.00 |
| 76543 | Singh | Finance | 80000.00 |
| 76766 | Crick | Biology | 72000.00 |
| 83821 | Brandt | Comp. Sci. | 92000.00 |
| 98345 | Kim | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+
12 rows in set (0.00 sec)
mysql> select * from course;
+-----------+----------------------------+------------+---------+
| course_id | title | dept_name | credits |
+-----------+----------------------------+------------+---------+
| BIO-101 | Intro. to Biology | Biology | 4 |
| BIO-301 | Genetics | Biology | 4 |
| BIO-399 | Computational Biology | Biology | 3 |
| CS-101 | Intro. to Computer Science | Comp. Sci. | 4 |
| CS-190 | Game Design | Comp. Sci. | 4 |
| CS-315 | Robotica | Comp. Sci. | 3 |
| CS-319 | Image Processing | Comp. Sci. | 3 |
| CS-347 | Database System Concepts | Comp. Sci. | 3 |
| EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 |
| FIN-201 | Investment Banking | Finance | 3 |
| HIS-351 | World History | History | 3 |
| MU-199 | Music Video Production | Music | 3 |
| PHY-101 | Physical Principles | Physics | 4 |
+-----------+----------------------------+------------+---------+
mysql> select * from teaches;
+-------+-----------+--------+----------+------+
| ID | course_id | sec_id | semester | year |
+-------+-----------+--------+----------+------+
| 76766 | BIO-101 | 1 | Summer | 2009 |
| 76766 | BIO-301 | 1 | Summer | 2010 |
| 10101 | CS-101 | 1 | Fall | 2009 |
| 45565 | CS-101 | 1 | Spring | 2010 |
| 83821 | CS-190 | 1 | Spring | 2009 |
| 83821 | CS-190 | 2 | Spring | 2009 |
| 10101 | CS-315 | 1 | Spring | 2010 |
| 45565 | CS-319 | 1 | Spring | 2010 |
| 83821 | CS-319 | 2 | Spring | 2010 |
| 10101 | CS-347 | 1 | Fall | 2009 |
| 98345 | EE-181 | 1 | Spring | 2009 |
| 12121 | FIN-201 | 1 | Spring | 2010 |
| 32343 | HIS-351 | 1 | Spring | 2010 |
| 15151 | MU-199 | 1 | Spring | 2010 |
| 22222 | PHY-101 | 1 | Fall | 2009 |
+-------+-----------+--------+----------+------+
15 rows in set (0.00 sec)
mysql> select name, title
-> from instructor natural join teaches, course
-> where teaches.course_id = course.course_id;
+------------+----------------------------+
| name | title |
+------------+----------------------------+
| Crick | Intro. to Biology |
| Crick | Genetics |
| Srinivasan | Intro. to Computer Science |
| Katz | Intro. to Computer Science |
| Brandt | Game Design |
| Brandt | Game Design |
| Srinivasan | Robotica |
| Katz | Image Processing |
| Brandt | Image Processing |
| Srinivasan | Database System Concepts |
| Kim | Intro. to Digital Systems |
| Wu | Investment Banking |
| El Said | World History |
| Mozart | Music Video Production |
| Einstein | Physical Principles |
+------------+----------------------------+
15 rows in set (0.00 sec)
mysql> select name, title
-> from instructor natural join teaches natural join course;
+------------+----------------------------+
| name | title |
+------------+----------------------------+
| Crick | Intro. to Biology |
| Crick | Genetics |
| Srinivasan | Intro. to Computer Science |
| Katz | Intro. to Computer Science |
| Brandt | Game Design |
| Brandt | Game Design |
| Srinivasan | Robotica |
| Katz | Image Processing |
| Brandt | Image Processing |
| Srinivasan | Database System Concepts |
| Kim | Intro. to Digital Systems |
| Wu | Investment Banking |
| El Said | World History |
| Mozart | Music Video Production |
| Einstein | Physical Principles |
+------------+----------------------------+
15 rows in set (0.00 sec)
From the result I see no difference and moreover I don't understand why should there be a difference. Unlike what the book the states, the natural join of instructor and teaches contains the attributes ID, name, dept name, salary, course id, sec id, semester, year.
The book is correct. These queries are not equivalent. Sample data(detp_name values are different):
INSERT INTO course(course_id, title, dept_name, credits)
VALUES (1, 'Course_1', 'DeptX', 10);
INSERT INTO instructor(ID, name, dept_name, salary)
VALUES (1, 'Prof X', 'DeptY', 10000);
INSERT INTO teaches(ID, course_id, sec_id, semester, year)
VALUES (1, 1, 10,10,2000);
Personally I wouldn't use any of proposed queries:
-- mixing natural join with old-comma syntax
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
--Output
--name title
--Prof X Course_1
-- natural join(potentially dangerous-depends on names of columns)
select name, title
from instructor natural join teaches natural join course;
-- 0 rows selected
And rewrite it as:
SELECT i.name, c.title
FROM instructor i
JOIN teaches t
ON t.ID = i.ID
JOIN course c
ON t.course_id = c.course_id;