Joining tables

[missing file: vizhelp.def]
file.htx
menu.htxJoin documentation

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:

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)

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.

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:

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:

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

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

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:

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

file.htxmenu.htxJoin documentation