12. SELECT: JOIN ON

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):

• t1 INNER JOIN t2 (INNER is the default and is usually omitted): Keep all elements in the cartesian product that satisfy the join condition.
• t1 LEFT OUTER JOIN t2: as INNER, but in addition for all rows of t1 that would vanish in the result (i.e., that have no match in t2) add a result row consisting of the row in t1 with NULL values where the row from t2 would be.
• t1 RIGHT OUTER JOIN t2: as LEFT OUTER, but this time all rows from t2 are retained.
• t1 FULL OUTER JOIN t2: as LEFT OUTER and RIGHT OUTER performed in sequence.