Getting the first URL alias for a node in a SQL query
Since the value in the src column in the url_alias table is predictable for nodes you can just query it directly, without the need for any joins:

$nid = 1; // node id
$src = 'node/' . $nid;
$query = db_query("SELECT dst FROM {url_alias} WHERE src = '%s'", $src);


If you definitely want to use a join though, something like this will work:

SELECT n.nid, ua.dst
FROM {node} n 
INNER JOIN {url_alias} ua ON ua.src = CONCAT('node/', n.nid)