I was looking for a *nix tool that takes two or more input files and joins them like SQL JOIN statement does. And I was not surprised at all. I found join
.
Say I have the following 2 files. Each of the files are sorted, and separated by colon. I wanted to join them according to their numeric IDs.
~ > cat companies
1:Apple
2:Microsoft
3:Google
4:Amazon
5:Intel
~ > cat locations
1:Cupertino, California, United States
2:Redmond, Washington, United States
3:Mountain View, California, United States
4:Seattle, Washington, United States
5:Santa Clara, California, United States
By default join
will merge input files based on first key. For example,
~ > join -t: companies locations
1:Apple:Cupertino, California, United States
2:Microsoft:Redmond, Washington, United States
3:Google:Mountain View, California, United States
4:Amazon:Seattle, Washington, United States
5:Intel:Santa Clara, California, United States
In case the input files have keys in different positions, it is possible to specify which column contains the key. For example, I changed the companies
file and shifted the key column to right.
~ > cat companies
Apple:1
Microsoft:2
Google:3
Amazon:4
Intel:5
This is the join
command I will be using. Note that -1 2
means the 1st file has its key in 2nd column.
~ > join -1 2 -t: companies locations
1:Apple:Cupertino, California, United States
2:Microsoft:Redmond, Washington, United States
3:Google:Mountain View, California, United States
4:Amazon:Seattle, Washington, United States
5:Intel:Santa Clara, California, United States