VMware ESXi and vSphere Cluster Management

Join Lines from Two Files by a Common Field with Linux join

Learn how to use Linux join to combine records from two text files by a matching field, select key columns, use delimiters, sort input, and troubleshoot common problems.

What the join command does

join combines lines from two text files when selected fields contain the same value. The selected field is called the join field or key. A key identifies corresponding records across files.

This is similar to a simple database join: one record from the first file and one matching record from the second file are emitted as a single output line.

A field is a portion of a record separated from other portions by whitespace or by a chosen delimiter. A delimiter is a character such as a space, slash, comma, or tab.

join matches values, not line positions. It does not automatically combine line 1 of one file with line 1 of another. For positional, line-by-line merging, use paste instead.

Default field handling

By default, join treats runs of blank characters as field separators. The first field of each input file is the default join field.

In the default output, the matching key is printed once, followed by the remaining fields from the first record and then the remaining fields from the second record. The key is not duplicated.

Basic operation

The basic command form is:

join file1 file2

For example, create these whitespace-delimited files:

1 Ada
2 Grace
3 Linus

Save that content as first_names.txt. Save the following as last_names.txt:

1 Lovelace
2 Hopper
3 Torvalds

Each file has a numeric identifier in its first field. Run:

join first_names.txt last_names.txt

The result is:

1 Ada Lovelace
2 Grace Hopper
3 Linus Torvalds

The first line from each file matches because both have the key 1. The key appears once, followed by Ada from the first file and Lovelace from the second. The same process produces the records for keys 2 and 3.

Choosing different join fields

The common value does not have to be the first field. Use -1 FIELD to select a field from the first file and -2 FIELD to select a field from the second file.

Field numbering starts at 1, not 0. Therefore, -1 3 means “use field 3 from the first file.”

Consider this data in file1.txt:

Ada/Engineer/101
Grace/Scientist/102
Linus/Developer/103

And this data in file2.txt:

Lovelace/101
Hopper/102
Torvalds/103

The matching identifier is field 3 in the first file and field 2 in the second file. Since the fields are separated by slashes, specify the delimiter and both field numbers:

join -t '/' -1 3 -2 2 file1.txt file2.txt

The output is:

101/Ada/Engineer/Lovelace
102/Grace/Scientist/Hopper
103/Linus/Developer/Torvalds

The selected key leads the default result, followed by the non-key fields from the first file and then the non-key fields from the second file. The slash is used between output fields because of -t '/'.

Why the delimiter matters

The default separator is blank whitespace. Slash-delimited records such as Ada/Engineer/101 are therefore seen as one field unless you use -t '/'. If the whole line is treated as one field, field selections such as -1 3 and -2 2 cannot find the intended keys.

Use the same delimiter definition when inspecting, sorting, and joining structured text. A delimiter must match the actual data format.

Sort both files before joining

join expects each input file to be ordered by its selected join field. The first-file ordering is based on the field selected by -1; the second-file ordering is based on the field selected by -2.

For the slash-delimited example, sort each file by the field that will be used as its key:

sort -t '/' -k3,3 file1.txt > file1.sorted.txt
sort -t '/' -k2,2 file2.txt > file2.sorted.txt
join -t '/' -1 3 -2 2 file1.sorted.txt file2.sorted.txt

sort -t '/' tells sort that fields are separated by slashes. The key ranges -k3,3 and -k2,2 sort only by the relevant field.

With unsorted input, join can produce incomplete or missing output and may report that an input file is not in sorted order. A matching value that exists in both files is not enough; the records must also be ordered as expected.

Understanding joined output

For the default output layout, join places the selected key first. It then writes the other fields from the first file, followed by the other fields from the second file. The common field is not repeated.

For example, joining:

101/Ada/Engineer

with:

Lovelace/101

using fields 3 and 2 produces:

101/Ada/Engineer/Lovelace

If a specific column order or a different selection of columns is required, the default layout may need adjustment with additional output-format options. First verify the default layout so that the placement of the key and remaining fields is understood.

Common limitations

• Field-based data: join is best for consistently delimited, field-based text records.
• Not line-position merging: If the requirement is to combine line 1 with line 1, line 2 with line 2, and so on, use paste rather than join.
• Duplicate keys: If a key occurs more than once in either file, multiple matching combinations may be emitted. Check whether that behavior is appropriate before processing the result.
• Exact values matter: Differences in case, whitespace, hidden characters, or formatting can prevent two apparently similar keys from matching.

Troubleshooting

No records are joined

Check that the intended fields were selected. The default is field 1 in both files, so a key in another position requires -1 and -2.

Also verify the delimiter. For slash-separated data, use -t '/'. Inspect the files carefully if keys may contain extra spaces, carriage returns, different capitalization, or other invisible characters.

The result is incomplete or the input is reported as unsorted

Sort both files by their selected join fields. Ensure that sort uses the same delimiter as join, and that its key ranges correspond to the join field numbers:

sort -t '/' -k3,3 file1.txt > file1.sorted.txt
sort -t '/' -k2,2 file2.txt > file2.sorted.txt

The output has unexpected columns or puts the key first

This is normally the default join output layout: the key is printed once at the beginning, followed by the non-key fields from each input record. If a different arrangement is needed, select or rearrange output fields with suitable output-format options after confirming which fields are present.

The task is to pair lines by position

Use paste when records should be combined according to line number rather than a shared key. join is for matching field values.

Exam-relevant summary

• join file1 file2 joins on the first field of both files by default.
• Fields are separated by blank whitespace unless -t CHAR specifies another delimiter.
• -1 FIELD selects the key field in the first file; -2 FIELD selects it in the second file.
• Field numbers start at 1.
• The selected key is normally printed once, followed by the remaining fields from both records.
• Both files must be sorted by their respective join fields.
• Use paste for line-by-line merging, not field-value matching.
• Repeated keys can produce multiple output combinations.

For more practice with this command, see Join Lines Of Two Files.