Joining Two Files on a Key: join vs awk, Measured
Four ways to merge the same two files on the same key. join on pre-sorted inputs is the fastest arm by a wide margin, and it still wins after paying for both sorts — but it refuses to run at all on unsorted input, and a duplicated key splits the two tools' answers in two.
Written November 10, 2026 · 12 min read
- The table: join wins even after paying for the sort
- The closest published work asserts ranges with no table
- The sort that join demands
- The duplicate key that splits the answer in two
What this guide is
Two files, one key, four arms, three reps each, run twice: once the night before and once fresh at build time, on the same box, so every number below is one of two medians from the same probe. File A is the lookup: 100,000 unique keys, u000001 through u100000, each with one of eight department values. File B is the fact table: 1,000,000 rows, each a key drawn uniformly from u000001 through u150000 with an amount from 1 to 99999. Two thirds of B's rows match a key in A, so the merged output is 666,655 lines of key|dept|amount, and every timed output was checked against a merged file Python computed independently from the same two inputs.
The four arms are the same job written four ways: join on pre-sorted inputs, sort both files and then join with the sort paid in full, an awk hash merge on the sorted fact file, and the same awk hash merge on the unsorted fact file. The question is the one people ask before merging two exports on the command line: is the specialist tool actually faster than the awk one-liner once you pay to sort, and what happens when the inputs are not sorted at all. The manual answers the second part in one sentence, and the probe checks it by running join on the unsorted files rather than taking the manual's word for it.
The answer is a correction in two directions. join on pre-sorted inputs is 6.74 times faster than the awk hash merge on the same sorted input, 0.0688 seconds against 0.4638 fresh, and it still wins 1.73 times after paying for both sorts, 0.2677 against 0.4638. But the sort is the price of admission: join on the unsorted inputs does not produce a slow answer or a wrong answer, it exits rc=1 after one output line with an error on stderr. And awk does not care about input order the way the manuals imply: it is only 1.34 times slower on unsorted input than on sorted input, 0.6221 against 0.4638, because the hash program is the same either way and only the order of the lookups changes.
The table: join wins even after paying for the sort
Wall seconds, medians of three runs. Every rep returned rc=0, every timed output matched the independently computed expected file, 666,655 lines on every rep, sha256 c5cfe66e… for the three sorted-order arms, and the same multiset for the unsorted awk arm.
The corpora: A is 100,000 lines, keys u000001 to u100000, departments cycling through eng, ops, sales, support, finance, legal, hr, infra; B is 1,000,000 lines, keys uniform over u000001 to u150000, amounts 1 to 99999. A sha256 35caee86…, B sha256 73b84e51…. The sorted copies are byte-sorted with LC_ALL=C sort -s -t'|' -k1,1.
joinon pre-sorted inputs: banked 0.0735 (0.0721 to 0.0796), fresh 0.0688 (0.0677 to 0.0718). Output byte-identical to the expected file on all three reps of both runs.sortboth files and thenjoin, total time: banked 0.2644 (0.2627 to 0.2721), fresh 0.2677 (0.2648 to 0.2708). Output byte-identical to the expected file on all three reps of both runs.awkhash merge, sorted B: banked 0.4629 (0.4593 to 0.4647), fresh 0.4638 (0.4634 to 0.4651). Output byte-identical to the expected file on all three reps of both runs.awkhash merge, unsorted B: banked 0.6080 (0.5981 to 0.6527), fresh 0.6221 (0.6127 to 0.6585). Output the same multiset as the expected file on all three reps of both runs.
Against join on pre-sorted inputs, the sort-and-join arm costs 3.60 times banked and 3.89 fresh; the awk arm on sorted input costs 6.30 banked and 6.74 fresh; the awk arm on unsorted input costs 8.27 banked and 9.04 fresh. The comparison that matters for a pipeline is sort-and-join against awk: join still wins 1.75 times banked and 1.73 fresh with the sort paid in full, and the awk arm on unsorted input is only 1.31 times slower banked and 1.34 fresh than the same program on sorted input.
One thing to read carefully in the table is what the sort-and-join arm actually pays for. It sorts both files from scratch on every rep, and the 0.2677 seconds is the whole pipeline: two sorts plus the join. A real pipeline sorts once and reuses the sorted files, so the sort cost is paid once per file, not once per merge. If the inputs arrive already sorted, the sort cost is zero and the join arm costs 0.0688 seconds; if they arrive unsorted and are merged once, the sort cost is the full 0.2677. The 1.73-times win over awk is the once-per-merge case, not the sorted-inputs case. Do not quote the 6.74-times gap as the saving unless your inputs are already sorted.
The 0.07 to 0.62 second cells sit near the spawn-and-read floor for a million-line file, so the exact ratios are one machine and the stable claims are the ones that hold on both runs: join on sorted inputs is several times faster than awk on the same input, join still wins with the sort paid, and awk barely notices unsorted input. The awk arms agree within 0.2 percent banked to fresh on sorted input, and the sort-and-join arm within 1 percent; only the join-sorted cell moved, 0.0735 to 0.0688, about 6 percent.
The closest published work asserts ranges with no table
The closest published work prints ranges with no corpus, no reps, and no named benchmark behind them. The copyprogramming piece on comparing columns across files lists, for simple string matching on large files, grep with -F at 0.1 to 0.5 seconds, awk with arrays at 0.3 to 0.8, and join with pre-sorted files at 0.2 to 0.6, and says the numbers come from 2025-2026 benchmarks without naming one. Its job is also a different job: it filters lines whose column value appears in another file, printing the matching lines, and never emits merged rows with fields from both files.
The Baeldung piece on filtering tabular data across two files runs awk, join, and q on a 3-row staff example with no timings. Its awk arm filters: it prints the staff-info rows whose id appears in staff-department, and never merges a field from the second file into the output. Its join arm does merge, and it hits the unsorted-input error on a 5-row file, which is the same error this probe measures at full scale. The piece also carries a q arm, a SQL-like tool this probe does not run, so the guide makes no measured claim about it.
The manuals state the semantics the probe checks. The join manual page says, in one sentence, "Important: FILE1 and FILE2 must be sorted on the join fields," and that comparisons honor the rules specified by LC_COLLATE, and that if the input is not sorted and some lines cannot be joined, a warning message will be given. The sort manual page says -s stabilizes the sort by disabling the last-resort comparison, and warns that the locale specified by the environment affects sort order and to set LC_ALL=C for the traditional byte order. The probe runs every sort under LC_ALL=C with -s, and the trap below shows what the manual's warning looks like in practice.
The sort that join demands
The trap file is the full corpus, unsorted, fed straight to join. join -t'|' a.txt b.txt exits rc=1 after one output line, with this on stderr:
join: /dev/shm/join-probe/b.txt:2: is not sorted: u045639|74227
join: input is not in sorted order
The same unsorted B through the awk arm produces all 666,655 rows. The failure is loud and almost immediate: join reads the second line of B, sees a key out of order, and stops. It does not silently produce a partial answer, and it does not fall back to a slower method. The one output line is the first matching row, not a usable result.
The practical rule follows from the trap, not from the manual: if the inputs are not sorted, either sort them first or use the awk arm. The sort is the whole price: sorting both files from scratch costs about 0.20 seconds on this corpus, which is most of the sort-and-join arm's 0.2677. If the files are already sorted, or sorted once and reused, the price is zero or paid once. If they are sorted under a different collation than join expects, join will report the same error, because comparisons honor LC_COLLATE and the sort that produced the files must use the same locale. This probe sorts and joins under LC_ALL=C throughout, and the sorted-input check (sort -c) passes on both sorted files on every run.
One correction belongs in the guide itself. The banking run's first probe run failed on its own harness, not on the measurement: GNU sort reorders equal keys by default, so the expected file built with Python's stable sort mismatched until the probe used sort -s. Plain sort without -s applies a last-resort full-line comparison that can reorder rows sharing a key, and that reordering breaks a byte-identical verification even when the merged rows are all correct. Every sort in this probe uses -s, and the guide states it plainly so a reader copying the commands does not chase the same mismatch.
The duplicate key that splits the answer in two
The trap pair is three lines each. File A has key u000042 twice, once with dept eng and once with ops, plus u000043 with sales. File B has u000042 once with amount 500, plus u000043 with 700 and u000044 with 900. The two tools answer the same question differently:
join: u000042|eng|500, u000042|ops|500, u000043|sales|700
awk: u000042|ops|500, u000043|sales|700
join emits two lines for the duplicated key, the cartesian product of the two A rows with the one B row. The awk program stores the dept in an associative array keyed by the first field, so the second A row overwrites the first, and it emits one line with the last dept, ops. Same question, different answers, and neither tool warns you: join's two lines are correct under its semantics, and awk's one line is correct under its program. The guide states which one it answers, because a pipeline that counts rows will get a different count from each tool the moment a key repeats.
The corpus itself has no duplicate keys in A, all 100,000 are unique, so the trap is the only place the difference shows.
Checklist
- Before joining two files, check whether both are sorted on the join field under the same locale: join exits rc=1 after one line on unsorted input, and sorting both files from scratch costs about 0.20 seconds on this corpus, most of the sort-and-join arm.
- Before quoting a join-to-awk ratio from this guide, check which case it is: 6.74 times on pre-sorted inputs, 1.73 times with the sort paid in full, and the sort is paid once per file, not once per merge, so reuse the sorted files.
- Before sorting for join, check the flags: use
LC_ALL=C sort -s -t'|' -k1,1, because plain sort reorders equal keys and breaks byte-identical verification, and a different collation than join expects produces the same not-sorted error. - Before merging on a key that can repeat, check which answer you want: join emits the cartesian product, two lines for one duplicated key, while the awk hash program emits one line with the last value, silently.
- Before trusting the awk-is-slower claim on unsorted input, check the gap: awk is only 1.34 times slower on unsorted input than on sorted input, because the hash program is the same either way and only the order of the lookups changes.
- Before reusing these numbers, check the setup: WSL2, 32 CPUs, join and sort GNU coreutils 9.4, GNU Awk 5.2.1, one thread per arm, 100,000 lookup keys, 1,000,000 fact rows, 666,655 matched rows, outputs written to tmpfs files, warm page cache.
Related guides on this site
- paste vs join, Measured: Fast by Position, Wrong by Key
- Editing a File In Place, Measured: sed -i vs perl -pi vs awk
- Are These Two Files the Same? cmp vs diff, Measured — Plain diff Costs 100x on Text and Nothing Extra on Binary
- Copying, Moving, and Splitting Files: Ten Measured Guides
Sources
- join(1) Linux man page — read 2026-11-10: FILE1 and FILE2 must be sorted on the join fields; comparisons honor the rules specified by LC_COLLATE; if the input is not sorted and some lines cannot be joined, a warning message will be given.
- sort(1) Linux man page — read 2026-11-10:
-sstabilizes the sort by disabling last-resort comparison; the locale specified by the environment affects sort order, set LC_ALL=C for the traditional byte order. - copyprogramming, Bash: Print Line If Column Value Is in Another File's Column — read in full 2026-11-10: asserts grep -F 0.1–0.5 seconds, awk 0.3–0.8, join with pre-sorted files 0.2–0.6, with no corpus, no reps, and no named benchmark; its job is filtering lines by column membership, not emitting merged rows. The closest published work; the gap this probe fills.
- Baeldung, Filtering Tabular Data Across Two Files — read in full 2026-11-10: awk filter, join, and q on a 3-row staff example, no timings; its join arm hits the same not-sorted error on a 5-row file. The unsorted-input error this probe measures at full scale.
- Veldica, paste vs join: Merging Two Files, Measured — the sibling guide: merges two files side by side on line number, a different job with no key and no lookup. This guide joins on a shared key; it does not re-teach the line-number merge.
- Veldica, comm vs grep vs sort|uniq vs Python: Set Intersection, Measured — the sibling guide: set intersection on one column, keys only, no merged fields. This guide emits merged rows; it does not re-teach the intersection table.
Keep Exploring
Paste a [Service] section and get a plain-language verdict on every sandboxing setting it finds.
Paste sshd -G output and get a hardening verdict on the values your server actually runs.
Paste a server block and get WebSocket, TLS, HSTS, and proxy-header verdicts.
Paste a systemd timer expression and get the specific reason it fails, the normalized form, and the past-date traps the built-in check hides.
The full sysadmin hub \u2014 systemd, cgroups, file tools, and measured Linux comparisons.