Skip to main content

UNIX: "join"

My buddy Pat Tufts showed me that Unix has a command called "join" that lets you do relational database style joins with text files. Let me show you how it works.

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 3
1 2
1 1
2 1
2 2
3 3
Next, I have all my items in another TSV file called items.tsv with fields item_id, item_name:
1 Hammer
2 Screw driver
3 Wrench
I want to print out the orders with item names instead of item ids.

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.tsv
Hence, I sort orders.tsv by its second field, treating the values as numbers. I end up with:
1 1
2 1
1 2
2 2
1 3
3 3
Remember, 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 -k1n
I 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 Hammer
1 Screw driver
1 Wrench
2 Hammer
2 Screw driver
3 Wrench
Hence, to fulfill order 1, I need a hammer, a screw driver, and a wrench.

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.

Comments

Howie Goodell said…
Hey JJ -- cool trick with the tab character! I had struggled awhile to figure that out. You saved me schlepping a gig of data in and out of Oracle just to do a join.

Cheers!
Howie Goodell
Thanks for the tip! and join is so much faster than sql join

Popular posts from this blog

Ubuntu 20.04 on a 2015 15" MacBook Pro

I decided to give Ubuntu 20.04 a try on my 2015 15" MacBook Pro. I didn't actually install it; I just live booted from a USB thumb drive which was enough to try out everything I wanted. In summary, it's not perfect, and issues with my camera would prevent me from switching, but given the right hardware, I think it's a really viable option. The first thing I wanted to try was what would happen if I plugged in a non-HiDPI screen given that my laptop has a HiDPI screen. Without sub-pixel scaling, whatever scale rate I picked for one screen would apply to the other. However, once I turned on sub-pixel scaling, I was able to pick different scale rates for the internal and external displays. That looked ok. I tried plugging in and unplugging multiple times, and it didn't crash. I doubt it'd work with my Thunderbolt display at work, but it worked fine for my HDMI displays at home. I even plugged it into my TV, and it stuck to the 100% scaling I picked for the othe

ERNOS: Erlang Networked Operating System

I've been reading Dreaming in Code lately, and I really like it. If you're not a dreamer, you may safely skip the rest of this post ;) In Chapter 10, "Engineers and Artists", Alan Kay, John Backus, and Jaron Lanier really got me thinking. I've also been thinking a lot about Minix 3 , Erlang , and the original Lisp machine . The ideas are beginning to synthesize into something cohesive--more than just the sum of their parts. Now, I'm sure that many of these ideas have already been envisioned within Tunes.org , LLVM , Microsoft's Singularity project, or in some other place that I haven't managed to discover or fully read, but I'm going to blog them anyway. Rather than wax philosophical, let me just dump out some ideas: Start with Minix 3. It's a new microkernel, and it's meant for real use, unlike the original Minix. "This new OS is extremely small, with the part that runs in kernel mode under 4000 lines of executable code.&quo

Haskell or Erlang?

I've coded in both Erlang and Haskell. Erlang is practical, efficient, and useful. It's got a wonderful niche in the distributed world, and it has some real success stories such as CouchDB and jabber.org. Haskell is elegant and beautiful. It's been successful in various programming language competitions. I have some experience in both, but I'm thinking it's time to really commit to learning one of them on a professional level. They both have good books out now, and it's probably time I read one of those books cover to cover. My question is which? Back in 2000, Perl had established a real niche for systems administration, CGI, and text processing. The syntax wasn't exactly beautiful (unless you're into that sort of thing), but it was popular and mature. Python hadn't really become popular, nor did it really have a strong niche (at least as far as I could see). I went with Python because of its elegance, but since then, I've coded both p