What is the relationship between the node and the node revision table in Drupal 7? I am trying to optimise the code in a Drupal 7 website by replacing node_load functions to retrieve only the title with custom SQL queries. 

So far, I have been successful in retrieving the same values. However I just cant seem to get the gist of getting the title from the latest revision of a node or retrieve any other value from the associated fields. 

Can anyone point me out regarding the concept of revisions from a table point of view?
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.