12. SELECT: JOIN ON

If your join criteria are more complex than simple equality, you can join ON.

SELECT dateobs as lswdate, t_min as appdate
FROM lsw.plates AS a
LEFT OUTER JOIN applause.main AS b
ON (dateobs BETWEEN t_min AND t_max)
WHERE dateobs BETWEEN 36050 and 36100

This particular query compares two archives of scanned plates, lsw.plates (from the Königstuhl observatories) and applause.main (from various other German observatories) and sees if lsw.plate’s observation date (dateobs) is within the exposure time of the other’s (which is between t_min and t_max).

The LEFT OUTER JOIN makes it so that every match on the lsw.plates side is retained. Where there is a simultaneous observation in Applause, the second column will have its MJD. Where there is no match, that second column will be NULL.

Of course, I have picked a WHERE clause for didactic reasons. If you drop it, you will get a large table with only very few matches in between (and you may need to go async; see below).

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.