If your join criteria are more complex, you can join ON:
SELECT TOP 20 source_id, h.hip FROM gaiadr1.tgas_source AS tgas LEFT OUTER JOIN hipparcos as h ON (tgas.phot_g_mean_mag BETWEEN h.hpmag -0.05 AND h.hpmag+0.05)
This particular query gives, for each source_id in tgas_source, all ids from ucas4 belonging to stars having about the same aperture magnitude as the mean g magnitude given in tgas_source. This doesn’t make any sense, but you may get the idea.
There are various kinds of joins, depending on what elements of the cartesian product are being retained. First note that in a normal join, rows from either table that have no “match” in the other table get dropped. Since that’s not always what you want, there are join variants that let you keep certain rows. In short (you’ll probably have to read up on this):