How to Use awk to Join Two Files on a Key Without Surprises

Two tables with a shared column are a join in database terms, and in the shell the standard tool is join, which demands both inputs sorted on the key and the same locale. Awk skips the sorting and gives you control over what happens with misses and duplicates. This post builds the common joins with a small employee and department example, on the technique from NR and FNR. Examples use GNU awk 5.2.

The data

1,ada,10
2,grace,20
3,linus,30
4,margaret,10

10,Engineering
20,Research

Inner join

awk -F, -v OFS=, 'NR == FNR { dept[$1] = $2; next } $3 in dept { print $1, $2, dept[$3] }' dept.csv emp.csv

1,ada,Engineering
2,grace,Research
4,margaret,Engineering

The small file loads first, keyed on its first column. For each employee, the third column is looked up and the line is printed only if a department exists. Linus, whose department 30 is unknown, disappears. That is correct for an inner join and silent, so count the misses if losing rows matters.

Left join with a default

awk -F, -v OFS=, 'NR == FNR { dept[$1] = $2; next }
  { print $1, $2, ($3 in dept ? dept[$3] : "UNKNOWN") }' dept.csv emp.csv

1,ada,Engineering
2,grace,Research
3,linus,UNKNOWN
4,margaret,Engineering

Every employee is kept, and unmatched ones get a marker. A marker string is better than an empty field because it survives later column splitting and stands out in review. Setting OFS with -v makes the comma in print output a comma, and -F, splits the input on it.

Duplicate keys in the lookup

awk -F, -v OFS=, 'NR == FNR { tags[$1] = ($1 in tags ? tags[$1] ";" $2 : $2); next }
  { print $1, ($1 in tags ? tags[$1] : "-") }' tags.csv emp.csv

If the lookup file has several rows per key, a plain assignment keeps only the last. Appending to the existing value, with a separator, keeps them all in one field. For a one-to-many join that should produce one output line per match, store a count and the values in numbered entries (tag[$1, ++n[$1]] = $2) and loop at lookup time.

Composite keys

awk -F, 'NR == FNR { price[$1 "|" $2] = $3; next } { print $0, price[$1 "|" $2] }' prices.csv sales.csv

Join on several columns by combining them into one string with a character that cannot appear in the data, or with the built-in subscript form price[$1, $2]. Use the same expression on both sides, including field order.

Compare with join

join -t, -1 3 -2 1 -o 1.1,1.2,2.2 <(sort -t, -k3,3 emp.csv) <(sort -t, -k1,1 dept.csv)

It works and is lean on memory, since it streams two sorted inputs, but the sorting, the field numbers and the -o format are easy to get wrong, and the output order follows the key. Choose join for very large sorted files and awk when the data is small enough to hold and the logic needs to be flexible.

Where this does not apply

CSV with quoted commas needs a CSV-aware tool; see awk and CSV or SQL over CSV. Header rows should be handled explicitly with FNR == 1. If the join is a recurring job, a real database such as SQLite does it with a single statement and an index; see SQLite as a local store. A lookup file with trailing carriage returns from Windows breaks every key; strip them with tr -d '\r'.

Joining on one field, and what happens to unmatched rows

The pattern is the same as for any two-file awk program: load the smaller file into an array keyed on the join field, then read the larger file and look each key up. An inner join prints a row only when the key exists in the array. A left join prints every row from the large file and supplies a default, such as a dash, for the missing lookup values, using (key in table) ? table[key] : "-". Remember that merely referencing table[key] creates an element, so always test with in before reading the value if you later count or loop the table. Decide what an unmatched row means for your data. Dropping them silently is easy, and hides data quality problems. Print a count of misses to standard error in END, so a join that lost half the rows announces it.

Choose the key field and the separators for each file explicitly. Files from different systems rarely agree, and a mismatch in the separator or the column number gives an empty join with no error.

Duplicates and composite keys

If the lookup file contains the same key twice, a simple assignment keeps only the last value and silently discards the earlier ones. Decide what you want: the first, the last, or all of them. To keep all, append to a string with a separator or use a counter and a second index, table[key, n], and loop over the matches when joining, which produces one output row per match as a database join does. A join on two fields uses a composite key, built with the comma form so the built-in SUBSEP separates the parts, on both sides in the same order. Mixed types in the key, such as a leading-zero number in one file and a plain number in the other, do not match as text, so normalize them. Trim spaces and carriage returns first, as stray characters at the end of the last field are the most common reason for no matches.

Comparing with the join command

The join command does the same job without an array in memory. It needs both files sorted on the join field with the same collation as join itself uses, which is the locale's, so run sort and join under the same LC_ALL. It supports a field separator with -t, key columns with -1 and -2, unmatched lines with -a or -v, and a filler for empty fields with -e together with -o to control the output columns. It warns when it sees unsorted input and may then produce an incomplete result, so do not ignore the message. It streams, so it handles files larger than memory after the sort, at the cost of the sort. Awk keeps the original order of the large file and needs no sorting, and join scales to bigger data. Pick awk when one side is small and order matters, and join when both are large.

When joins become the main activity, such as several keys, several sources and aggregations, load the files into SQLite with .import and write the join as SQL, which is easier to read and check than a chain of awk programs.

Performance and checking the result

Awk joins are linear in the size of the inputs plus the cost of building the table, so they are fast for lookups of a million keys on a modern machine. The cost that matters is memory for the lookup file, and the time to read the larger file once. Verify a join by counting. The number of output rows in an inner join should equal the number of large-file rows with a match, and for a left join it should equal the number of large-file rows, plus extra rows if duplicate keys were expanded. Compare these counts against a quick grep -c or sort | uniq on a sample, and print the number of unmatched rows. A join that returns fewer rows than expected without a message is the dangerous kind, so make the program say how many records it dropped.

See the coreutils join manual and gawk multidimensional arrays.