Add a subquery in a WHERE condition, using hook_views_query_alter()
Ideally I think its right to use views_join_subquery. But the subquery you are using is with IN keyword is always slow and will produce same result as using a LEFT JOIN joining with node.nid field. 

You can try creating a views_join and adding the url_alias table by add_field method while the node table will be the left table. 

I don't have the code at the moment but you can have a look at the below links. 
https://stackoverflow.com/questions/6851945/add-table-join-where-and-order-by-to-views-query-in-views-query-alter

http://www.wordpressecoder.com/2014/01/how-to-alter-views-query-with-multiple.html 
http://www.yellowpencil.com/blog/last-ditch-fix-programmatically-changing-drupal-7-view