title: Self-Referencing Foreign Key Select
body: <p>I have a table structure:</p>
  
  <p>Region:</p>
  
  <ul>
  <li>id            (key)</li>
  <li>parent_region (foreign key - references another region)</li>
  <li>name</li>
  </ul>
  
  <p>Assume I have the following 3 rows:</p>
  
  <pre><code>[ id, parent_region, name ]
  (0, NULL, "USA")
  (1, 0, "Georgia")
  (2, 1, "Atlanta")
  </code></pre>
  
  <p>I want to be able to select Atlanta, Georgia, USA by querying on the id:</p>
  
  <pre><code>SELECT ... 
    FROM region 
   WHERE id = 2
  </code></pre>
  
  <p>...and return the 3 rows.  I'm using Java/JDBC so obviously I could programatically select the 3 rows one by one... but I'm curious if there is a clever SQL way of doing this.</p>
  
  <p>I'm using MySQL but I'd also be curious about an Oracle or Informix solution as well. </p>