How a join works

Optional reading: this explains what happens out of sight. See The data centre for what this section is, and Joining datasets for how to do it.

It runs in the database and makes a new table

A join is worked out by PostgreSQL itself. No rows are sent to your browser, and none pass through the application server: one statement reads the inputs and writes the result as a new table. That table is swapped in under the name you chose in a single step, together with its entry in the list of datasets. A join that fails or is cancelled therefore leaves nothing behind, and if you gave it the name of a dataset that exists, the old one stays until the new one is complete.

Because the result is a table of its own, it is a copy. The datasets it was made from are not touched, and the joined dataset does not follow changes made to them afterwards: calculate a column on a source, or delete rows from it, and the join is as it was. Join again to pick the change up.

Which rows go in, and how they match

Only the rows you can see. Rows an input’s filter hides are not joined. Clear the filter first if you want them.
Keys match on their text. The integer 1 and the string "1" are the same key, which is what lets a number column in one file match an identifier column in another. A joined key column whose inputs are of different types is a text column.
A row with no key never matches, and is left out, whichever kind of join you chose.
A repeated key gives every pairing. If two rows of one input and three of another share a key, the result has six rows for it.
Inner, Left and Full keep, respectively, the keys found in every input, those of the first, and those of any.
A column name that two inputs share is prefixed with the name of the dataset it came from.

The rows are numbered afresh

A joined row is a combination of rows, so it is not any one source row, and it gets a new id: the rows are numbered from 0, in the order of the inputs’ own rows. The ids of the sources mean nothing in it, which is why a selection made on a source does not carry across a join.

Structures

Only one structure column keeps its chemistry search: the first structure column in the joined dataset that comes from a dataset that has been indexed. Any other structure column is plain text: it can still be drawn, but not searched.

The join does not build another index. It uses the same one as the dataset the structures came from, and adds a hidden column holding each row’s molecule number, so a row can find its molecule even though the row ids changed. Three things follow:

Joining costs nothing for the index, however large it is.
A row with no structure, such as the unmatched side of a left join, matches no search, and is in neither a search nor its negation.
The two datasets now depend on one index. It is kept for as long as either uses it, and when you make a filter permanent on one of them, the molecules the other still uses are kept.

If none of the joined datasets has been indexed, the result is not indexed either; index it afterwards if you want to search it.