If your join criteria are more complex, you can join ON:
SELECT TOP 20 hipno, name FROM dmubin.main AS dmu LEFT OUTER JOIN rave.dr3 AS rave ON (dmu.mv BETWEEN rave.imag-0.05 AND rave.imag+0.05)
This particular query gives, for each hipno in dmubin, all names from rave belonging to stars having about the same I magnitude as the visual magnitude given in dmubin. 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):