12. SELECT: JOIN ON

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

• 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 JOINt2: 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.