What is the relationship between the node and the node revision table in Drupal 7?
I believe this is what you're looking for:

function MYMODULE_get_node_title($nid) {
  // Using Drupal's DB API.
  $query = db_select('node_revision', 'r')
    -&gt;fields('r', array('title'))
    -&gt;addTag('node_access');
  $query-&gt;leftJoin('node', 'n', 'n.vid = r.vid');
  $query-&gt;condition('n.nid', $nid);
  $title = $query
    -&gt;execute()
    -&gt;fetchField();

  // Using a custom query. This might help you understand better what's the
  // exact relationship. This approach is a bit faster as well.
  $title = db_query("SELECT r.title
                     FROM
                      {node} n,
                      {node_revision} r
                     WHERE
                      n.nid = :nid AND
                      n.vid = r.vid", array(':nid' =&gt; $nid))-&gt;fetchField();

  return $title;
}


To understand the relationship look at the second approach, using a custom raw query.
vid stands for version ID and if you look at the node table you will see that there is a column named vid.
This is the active version of that node and is the ID you should use to get all field values and other attributes of that piece of content.

Hope this helps.