Let's suppose I have a bunch of purchase orders in a TSV (i.e. tab separated values) file called orders.tsv. The fields are order_id, item_id:
1 3Next, I have all my items in another TSV file called items.tsv with fields item_id, item_name:
1 HammerI want to print out the orders with item names instead of item ids.
2 Screw driver
The first thing I need to do to use join is pick the join field. Clearly, I'm going to join on the item_id field. The next thing is to make sure both files are sorted by item_id. This is a strange requirement if you're used to RDBMSs, but it makes sense if you think of how it's implemented. Clearly, items.tsv is already sorted by the item_id. However, I'll need to sort orders.tsv by item_id:
sort -k2n orders.tsv > orders-sorted.tsvHence, I sort orders.tsv by its second field, treating the values as numbers. I end up with:
1 1Remember, the fields are order_id and item_id. Now, I can join the two files.
join -1 2 -2 1 -t ' ' -o 1.1,2.2 orders-sorted.tsv items.tsv | sort -k1nI join the first file (orders-sorted.tsv) on its second field (item_id) with the second file (items.tsv) on its first field (item_id). My field delimiter is tab (to type a literal tab into a shell command, use cntl-v and then tab). I output the fields 1.1 (order_id) and 2.2 (item_name). Because the output comes out sorted by item_id instead of order_id, I need to sort it again by the first field (order_id). I end up with:
1 HammerHence, to fulfill order 1, I need a hammer, a screw driver, and a wrench.
1 Screw driver
2 Screw driver
Clearly, this isn't as easy and convenient as using a relational database. However, it's neat to see that it exists. My buddy Pat warned me though that dealing with all the required sorting and implicit field numbers can drive you crazy. Furthermore, it's really easy to end up with garbage.
Still, it's fun to see what's possible using plain text files and a shell. I've always heard that the "old timers" were able to implement relational databases using sed, awk, and sh, but it seems so much more plausible now that I've seen join in action.