How to join different VizieR tables in the web vizier application.

1  Why joining tables?

Joining tables presents the data issued from different tables in a single logical entity (or table). Tables can be joined when several tables contain the same property (typically, they refer to the same astronomical object). These tables can be issued from the same catalogue or not.

Example:
The Tycho catalogue contain 2 tables I/239/hip_main and I/239/tyc_main, and we wish to display the following fields:

• the HIP number (present in the both tables)
• the V-I magitude (present only in the hip table)
• the Tycho number (present in the tyc table only)

2  Examples

The two following examples show how to make a join query in VizieR. In both examples, we will use the Hipparcos and Tycho catalogues (I/239).

2.1  Simple join

We will join the tables I/239/hip_main and I/239/tyc_main on the field HIP (a natural join in relational terminology)

• type tycho in the main form of VizieR
• select the tables I/239/hip_main and I/239/tyc_main and click on button Join selected tables

• you see the HIP column with a join logo and new choices, indicating that you will do a join query. Then click on Submit button.

• the result presents the 2 tables as a single entity. Two colors are used to show the fields issued from the different tables. The column in yellow indicates the field used to make the join query.

  The result combines the two tables I/239/main and I/239/tyc_main, and each line gives data from both tables for the same Hipparcos number.

2.2  Join between two tables that are not always connected

In relational terminology it means making an extern or outer join. We will make a join and print all rows of a given table even if there is no corresponding row in the second table.

• type tycho in the main form of VizieR
• select the tables I/239/hip_main and I/239/hip_va_1 (variable stars in Hipparcos), and click on button Join selected tables

• click on the radio button located in the 4th column of the row entitled table (1): .

  Then click on the Submit button

• the result is a single table which combines the two tables into a single entity; in this resulting table, the columns corresponding to the hip_va_1 table are empty for the stars having no variability data.

  If you execute the same query without setting the outer join (radio button not checked), the query returns only the rows with a HIP number present in both tables.

3  Detailed join possibilities

You can set one, two or more joins on two or more tables. You are just limited to one outer join (see radio button on fig Help_img) if you want to display rows even if there is no connection with the other tables.

If you want make a join, you can specify it in the table selection step: for this purpose, click on the join selected tables button. You can still select a join at the last step (see figure Help_img) by clicking on the fields image or (join link).

After selecting a field for a join, you will see some colored lines which confirm that you will make a join. The third column lists some information about the role of the field:

• unique reference inside the catalogue: this is a secure link (in SQL: a primary key)
• linked by other tables inside the catalogue: this is a secure link (in SQL: a foreign key)
• Warning: could be slow: this kind of query could be slow (in SQL: field not indexed)

When you have selected more than 2 tables, it is possible to make a join on a field which does not exist in one of the tables. In that case, the query result consists in the cartesian product between the table which lacks the field and the result of the join query between the other tables. If this happens, a warning message appears, located on the first colored lines,

4  When joining tables?

The VizieR web interface suggests the good candidate columns for a join query. Two different symbols are used to mark such columns:

• the columns which have the role of primary/foreign keys appear in the web page with an image with bright color; queries using such joins are secure.

• the other columns which may be used for a join they appear in the web page with an image with light color. This method is not secure, but can produce some interesting results.

There are some restrictions about this second (unsecure) join:

• two fields issued from differents tables can have the same name but describe an other property.
• even if they describe the same property, the two fields may have no common value

Example:
In the Hipparcos/Tycho catalogue with tables I/239/hip_main and I/239/tyc_main:

• the HIP number (present in both tables) is of type secure
• the Plx (parallax) value (also present in both tables) is not secure

Special notes about coordinate fields:
we draw attention on a join query using a coordinate field. In that case the query will connect only data which have exactly the same value (value understood as a string). This sort of join does not use a cone search method: therefore, two tables which contain the same object but with slightly different coordinates will give an empty result.

5  Restrictions

Currently, only catalogues actually managed by a DBMS can be joined; this excludes the very large surveys like 2MASS, USNO-B1 or SDSS which are managed by dedicated search engines.

6  Specifying a join with the ASU syntax

In the asu syntax used by the vizquery program, the following arguments are used for a join:

• -joincol=[table.]column: the column to make the inner join.
• -outjoin=[table.]column: the column corresponding to the referent in the outer join.
  Note that the 3 arguments -outjoin, -joincol and -out arguments are required to generate a valid query. In SQL terms, the selection
  select A.f,... from A left join B on A.f=B.f
  corresponds to the asu arguments:
  -source=A B
  -joincol=A.f
  -outjoin=A.f
  -out=...
  

Examples:

1. get details, from the Hipparcos catalog, about stars which are both multiple and variables. This request means a natural join, on the HIP column, of the tables h_dm_com and hip_va_1 and can be written:
   http://vizier.u-strasbg.fr/viz-bin/VizieR-4?-source=I/239/h_dm_com,hip_va_1&-joincol=HIP

2. get for Hipparcos stars numbered between 50 and 100 the variability type of Hipparcos stars, if any. This means an outer join from the Hipparcos main table hip_main and the 2 variability annexes hip_va_1 (periodic variables) and hip_va_2 (non-periodic variables) and can be written: http://vizier.u-strasbg.fr/viz-bin/VizieR-4?-source=I/239/hip_main,hip_va_1,hip_va_2&HIP=50..100&-joincol=I/239/hip_main.HIP&-outjoin=I/239/hip_main.HIP&-out=HIP,RA(ICRS),DE(ICRS),Plx,SpType,VarType

last update: 13 Feb 2024