How to map url_alias table to node table?
They are not related because url_alias table contains all the URL aliases. You can use MySQL CONCAT to concatenate strings (fields or string literals). 

$results = db_query("
  SELECT 
    n.nid, n.title, 
    a.source, a.alias 
  FROM {node} n 
  LEFT JOIN {url_alias} a 
    ON CONCAT('node/', nid) = source
  ")-&gt;fetchAll();
foreach ($results as $result) {
  dpm($result);
}