title: NATURAL JOIN vs WHERE IN Clauses
body: <p>Recently, I dealt with retrieving a large amount of data which consists of thousands of records from a MySQL database. Since it was my first time to handle such large data set, I didn't think about the efficiency of the SQL statement. And the problem comes.</p>
  
  <p>Here are the tables of the database
  (It is just a simple database model of a curriculum system):</p>
  
  <p>course: </p>
  
  <pre><code>+-----------+---------------------+------+-----+---------+----------------+
  | Field     | Type                | Null | Key | Default | Extra          |
  +-----------+---------------------+------+-----+---------+----------------+
  | course_id | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
  | name      | varchar(20)         | NO   |     | NULL    |                |
  | lecturer  | varchar(20)         | NO   |     | NULL    |                |
  | credit    | float               | NO   |     | NULL    |                |
  | week_from | tinyint(3) unsigned | NO   |     | NULL    |                |
  | week_to   | tinyint(3) unsigned | NO   |     | NULL    |                |
  +-----------+---------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>select: </p>
  
  <pre><code>+-----------+------------------+------+-----+---------+----------------+
  | Field     | Type             | Null | Key | Default | Extra          |
  +-----------+------------------+------+-----+---------+----------------+
  | select_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | card_no   | int(10) unsigned | NO   |     | NULL    |                |
  | course_id | int(10) unsigned | NO   |     | NULL    |                |
  | term      | varchar(7)       | NO   |     | NULL    |                |
  +-----------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>When I want to retrieve all the courses that a student has selected (with his card number),
  the SQL statement is</p>
  
  <pre><code>SELECT course_id, name, lecturer, credit, week_from, week_to
  FROM `course` WHERE course_id IN (
      SELECT course_id FROM `select` WHERE card_no=&lt;student's card number&gt;
  );
  </code></pre>
  
  <p>But, it was extremely slow and it didn't return anything for a long time.
  So I changed <code>WHERE IN</code> clauses into <code>NATURAL JOIN</code>. Here is the SQL,</p>
  
  <pre><code>SELECT course_id, name, lecturer, credit, week_from, week_to
  FROM `select` NATURAL JOIN `course`
  WHERE card_no=&lt;student's card number&gt;;
  </code></pre>
  
  <p>It returns immediately and works fine!</p>
  
  <p>So my question is:</p>
  
  <ul>
  <li>What's the difference between <code>NATURAL JOIN</code> and <code>WHERE IN</code> Clauses?</li>
  <li>What makes them perform differently?
  (Is that maybe because I doesn't set up any <code>INDEX</code>?)</li>
  <li>When shall we use <code>NATURAL JOIN</code> or <code>WHERE IN</code>?</li>
  </ul>