Querying from multiple tables: Joining

Take another look at the
Schema Browser. Data on the same objects are often stored in different
tables and/or views. For example, a galaxy's magnitudes are stored in the photoObj
view, its redshift in the specObj view, and the plate used to observe
its spectrum in the plateX table.

Quite often, you will want to compare information from many
different tables. For example, making a Hubble Diagram to show the expansion
of the universe requires some measure of brightness, such as magnitude,
and redshift. Magnitude is in the photoObj table; redshift is in the
specObj table. How do you search both tables?

You can search multiple tables using SQL's join syntax. You will need
to designate one table/view as the base table onto which other tables will be
temporarily joined - at this stage it doesn't matter which is the base table and
which table(s) are joined to it. Put the name of the base table in the FROM block
of the query, and give it a shorthand name to refer to it. For example, if you
want to use photoObj as the base table, you might say:

FROM photoObj p

List the other tables you want to search, one line at a time, using SQL's
JOIN...ON syntax. On each line, use the keyword JOIN, the name
of the table, a nickname for the table to be joined, and the keyword ON.
After the ON, specify the name of the column you want to use to do the
join, both in the base table and in the table you want to join. The column may
be called something different in the two tables, so study the Schema Browser
carefully. For example, so temporarily join the photoObj and specObj views,
you would say:

As this query demonstrates, you don't have to join all tables on to the base table -
you can join tables onto other join-ed tables as well.

To join two tables, look for common entries - when the same column
appears in both tables. The columns that appear after the ON keyword,
connected by an equals sign, will be the names of the same column
in each table.

To find columns to use in joining tables, look in the
Schema Browser.
Find places where the same column name appears in more than one table.
You can use that column in the where block to join the tables.

Naming tables in the SELECT and WHERE blocks — either by their
full name or by the nickname you specify — is important because
sometimes columns in different tables have the same name, yet contain
different data. For example, the PhotoObj table contains the right
ascension and declination of the object in the "ra" and "dec" columns.
The PlateX table also has ra and dec columns, but these are the ra
and dec of the telescope while it is observing a single plate, not
the ra and dec of an individual object on the plate. So photoObj.ra
and plate.ra mean totally different things!

In addition to the join statements, the query above includes two
other commands in the where block. The command specObj.class = 'qso'
tells the query to return quasars only, and the command
specObj.zWarning = 0 tells the query to return only objects for
which we have well-measured
redshifts. Again, you can add more restrictions to your query in terms
of colors, magnitudes, ra and dec or any number of parameters.

Try It!

Click Submit below to run the query. If you would like, try rewriting the
query using one-letter nicknames for all the tables.

Click Next to get some more practice using joins.

Format

HTML

XML

CSV

Enter your SQL query in the text box.
The query is limited to 10 minutes and 100,000 rows.