Joining tables
[missing file: vizhelp.def]
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:
- 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)
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:
- 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
- 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