title: Using WHERE clause on NATURAL JOIN
body: <p>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:</p>
  
  <pre>
  department(<b>dept_name</b>, building, budget)
  course(<b>course_id</b>, title, dept_name, credits)
  instructor(<b>ID</b>, name, dept_name, salary)
  section(<b>course_id</b>, <b>sec_id</b>, <b>semester</b>, <b>year</b>, building, room number, time_slot_id)
  teaches(<b>ID</b>, <b>course_id</b>, <b>sec_id</b>, <b>semester</b>, <b>year</b>)
  prereq(<b>course_id</b>, <b>prereq_id</b>)
  </pre>
  
  <p>In the book the following query is stated:</p>
  
  <p>"List the names of instructors along with the the titles of courses that they teach".</p>
  
  <p>The answer given by the book is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select name, title
  from instructor natural join teaches, course
  where teaches.course id = course.course id;
  </code></pre>
  
  <p>Furthermore, the writer goes:</p>
  
  <p>"In contrast the following SQL query does not compute the same result:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select name, title
  from instructor natural join teaches natural join course;
  </code></pre>
  
  <p>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. "</p>
  
  <p>Now I tried both queries to see myself the difference.</p>
  
  <pre><code>mysql&gt; 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&gt; 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&gt; 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&gt; select name, title
  -&gt; from instructor natural join teaches, course
  -&gt; 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&gt; select name, title
      -&gt; 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)
  </code></pre>
  
  <p>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 <em>instructor</em> and <em>teaches</em> contains the attributes <em>ID, name, dept name, salary, course id, sec id, semester, year</em>. </p>