The main extension of ADQL wrt SQL is addition of geometric functions. Unfortunately, these were not particularly well designed, but if you don’t expect too much, they’ll do their job.
Keep the crossmatch pattern somewhere handy (everything is in degrees):
SELECT TOP 5 source_id, tgas.ra, tgas.dec, tm.raj2000, tm.dej2000, hmag, e_hmag FROM gaiadr1.tgas_source as tgas JOIN twomass AS tm ON 1=CONTAINS ( POINT('ICRS', tm.raj2000, tm.dej2000), CIRCLE('ICRS', tgas.ra, tgas.dec, 1.5/3600))
In theory, you could use reference systems other than ICRS (e.g., GALACTIC, FK4) and hope the server converts the positions, but I’d avoid constructions with multiple systems – even if the server implements the stuff correctly, it’s most likely going to be slow.
Compare the total proper motions of the top 5000 stars in hipparcos and tycho2, together with the respective identifiers (hip for hipparcos, id for tycho2). Use a positional crossmatch with, say, a couple of arcsecs.