11. SELECT: JOIN USING

The tricky point in ADQL is the FROM clause. So far, we had a single table. Things get interesting when you add more tables: JOIN.

SELECT TOP 10 h1.ra, h1.dec, h1.hip, t1.hip
FROM hipparcos AS h1
JOIN tycho2 AS t1
USING (hip)
Check the tables in the Table Metadata shown by TOPCAT: astroref is from hipparcos, hp_mag from hipparcos_newreduction; both tables have a hip column.

JOIN is a combination of cartesian product and a select.

FROM hipparcos AS h1
JOIN tycho2 AS t1
USING (hip)

yields the cartesian product of the hipparcos and tycho2 tables but only retains the rows in which the hip columns in both tables agree.

Note that while the hip column we’re joining on is in both tables but only occurs once in the joined table.


Markus Demleitner

Copyright Notice