Postgres 8.3: Many ways to join a table

| Thursday, July 30, 2009

In BioSQL, PhyloDB module, there are tables like this:

This becomes a problem when I want to retrieve values that are related to one tree. For example, here are some data from the table

22"Single Tree"0
26"Ladiges, P. Y.; Humphries, C. J."0
27"A cladistic study of Arillastrum, Angophora, and Eucalyptus (Myrtaceae). "0
28"Transformed cladistic; character compatibility; branch and bound, and Farris-Wagner methods ...0

There is another table term that has term_id, and name. For example, the term (term_id=2) has the name 'dc.title'
I found a few ways to join the data and create a tabular format that each row belongs to one tree_id.

First, I can use what is called self join :

Here I used different table aliases for the same table, and each alias represents a different instance of the table.

Another way is to use FULL OUTER JOIN:

Interestingly, I also tried to use the PostgreSQL XML support to generate a piece of XML from that table. Here instead of using JOIN, I used UNION:

This will produce some thing like:

For a table with 5287 different tree_id, and a total of 42320 rows, the three method takes 1080ms, 1136ms, 1338ms (average of three tests). With no surprise that the last method takes longer as it puts the XML together.