🚀 go-pugleaf

I often use join(1) these days and I have been pleased at the
speed of GNU join relative to Sun's /bin/join.

I have a suggestion for an enhancement to join, as follows.  Normally
we tell join the field number of "join field" in file 1 and in file 2.
I often have to deal with joining on a combination join-field
constructed by concatenating many fields existing in the file.

For example, in dealing with financial data for companies, there might
be a field containing the company name and another field containing
the accounting year.  Then the correct field to join data between
two relations is often the combined-field formed by concatenating
the (string) company name with the (string) accounting year.

(I do this using an awk preprocessing stage, but from my limited
knowledge of relational databases, I suspect this is a problem faced
often enough to be considered a "core facility".  Another reason to
put this extra functionality into join instead of asking users to do
it using awk is: when you have two files where a join on a combined
field is needed, it can't be done 'on the fly' in a pipe.  You have to
keep scratch files, and the general tedium gets a LOT worse.  So: No,
I don't think this is a case of feeping creatures).

In short, I think it would be nice to have a join which could deal
with a syntax like this:

        join -j1 2 -j1 3 -j1 1  -j2 7 -j2 9

This would mean:
        a) The join field in file 1 is formed by concatenating
        fields 2, 3 and 1
        b) The join field in file 2 is formed by concatenating
        fields 7 and 9

(Thus the order in which multiple join fields are specified is
important, since the concatenation is done left-to-right).

Sensible defaults still work, e.g.

        join -j1 3 -j1 2 

would concat fields 3 and 2 in file 1 and use the first field in file2
as the join field.

Everything else in the syntax of join should go through unchanged.
It would be nice to have one last special touch: with -o, we need
a way to represent "the key field", which (in this framework) might
not have been present in the file.  Could we use field 0 for this
purpose?  Then the flags "-o 1.0" would mean "key field from file 1".

Thanks,

        -ans.
-- 

Ajay Shah, (213)749-8133, ajayshah@usc.edu