How to Merge Files Using a Common Field with the join Command in Linux

When working with flat-file databases or CSV exports in a Linux terminal, you will often find yourself needing to merge data from two separate files. While you could write a complex Python script or awk command to achieve this, Linux includes a native utility called join designed specifically to merge lines from two files based on a shared, common field, operating exactly like an SQL JOIN in a relational database.

How the join Command Works

To use the join command successfully, both files must share a common column (like an ID number), and crucially, both files must be sorted based on that specific column. If the files are not sorted, the command will fail or skip lines.

Imagine you have two files separated by spaces. employees.txt contains an ID number and a name. departments.txt contains the exact same ID number and a department name.

employees.txt:
101 Alice
102 Bob
103 Charlie

departments.txt:
101 Engineering
102 HR
103 Sales

To merge these two files based on their shared ID number (which is the first column in both files), simply run:

join employees.txt departments.txt

The terminal will instantly output a beautifully merged relational table:

101 Alice Engineering
102 Bob HR
103 Charlie Sales

Joining Files on Specific Fields

By default, join always looks at the very first column (field 1) in both files to find a match. However, real-world data is rarely this clean. What if the shared ID number is in the second column of the first file, but the third column of the second file?

You can specify exactly which fields to join on using the -1 (file 1 field) and -2 (file 2 field) flags.

join -1 2 -2 3 employees.txt departments.txt

This command tells the utility: “Look at field 2 in employees.txt and try to match it against field 3 in departments.txt.”

Handling Custom Delimiters

By default, the join command assumes your columns are separated by empty spaces or tabs. If you are working with a standard CSV file, your data is separated by commas. You must explicitly tell the utility to use a comma as the delimiter by passing the -t flag.

join -t ',' employees.csv departments.csv

This ensures the command correctly parses the commas, preventing it from treating an entire row as a single, massive string of text.