join

What it does

join combines lines from two files that share a common field value, much like a SQL JOIN on a key column. It reads both files in parallel and matches rows where the specified join field is equal, printing the join field followed by the remaining fields from both files. The catch — and the thing that trips up almost everyone the first time — is that both input files must already be sorted on the join field, since join uses a sequential merge, not an index lookup.

Beginner examples

• join file1.txt file2.txt — join on the first field of each file (default), both must be sorted on it
• join -t',' file1.csv file2.csv — join comma-delimited files on the first field
• join -1 2 -2 1 file1.txt file2.txt — join field 2 of file1 to field 1 of file2
• sort -k1,1 file.txt > file.sorted — the required prep step before joining

join -t',' users.csv orders.csv

Advanced examples

• Perform a left outer join, keeping rows from file1 that had no match: join -a 1 -t',' employees.csv salaries.csv
• Perform a full outer join, keeping unmatched rows from both sides: join -a 1 -a 2 -t',' left.csv right.csv
• Control exactly which fields appear in the output: join -o 1.1,1.2,2.3 -t',' a.csv b.csv
• Replace missing values with a placeholder in an outer join: join -a 1 -e "NULL" -o auto -t',' a.csv b.csv
• Chain with sort inline using process substitution instead of pre-sorted temp files: join <(sort -k1,1 a.csv) <(sort -k1,1 b.csv)

join <(sort -t',' -k1,1 users.csv) <(sort -t',' -k1,1 orders.csv)

Common mistakes

• Forgetting to sort both files on the join field first — join will silently produce incomplete or wrong results (or an error about unsorted input) instead of a correct join.
• Sorting by the wrong field or wrong delimiter, so the sort order doesn't match what join expects for its comparison — the sort and join delimiter/field settings must agree.
• Assuming join behaves like an inner join by default and finding rows silently missing — that's expected; use -a to include unmatched rows if you need an outer join.
• Not setting -t for CSV or other delimited files and getting a "field" that's actually the whole line, since the default delimiter is whitespace.

Tips

• Use process substitution (join <(sort -k1,1 a) <(sort -k1,1 b)) to avoid creating temporary sorted files for one-off joins.
• Use -o to control output field order and avoid duplicate join-key columns cluttering the result.
• Verify sort order matches join expectations with sort -c before joining large files, to catch a "not sorted" mismatch before wasting time.

Best practices

• Always sort both files on the exact join field, with the same delimiter, immediately before joining — treat "sort then join" as one atomic step, similar to "sort then uniq".
• Prefer join -o to explicitly select output fields in scripts, rather than relying on the default full-row output, so downstream tools can rely on a stable column layout.
• For anything beyond a simple two-file equi-join (multi-key joins, joins requiring transformation), reach for awk or a proper database/CSV tool instead of stretching join past its design.

Real-world use cases

• Merging a CSV of user IDs and names with a CSV of user IDs and order totals to build a combined report: join -t',' -1 1 -2 1 users.csv orders.csv.
• Cross-referencing a list of active employee IDs against a payroll export to find employees missing pay records: join -a 1 -t',' active_ids.csv payroll.csv.
• Combining sorted log files from two different services on a shared request ID to trace a request across systems: join -1 2 -2 1 service_a.log service_b.log.

Common interview questions

• Why must input files be sorted before using join? join performs a sequential merge of both files assuming ascending order on the join field; without sorting, it can't correctly line up matching rows across the two streams.
• How does join differ from a SQL JOIN? Conceptually the same operation — combining rows on a matching key — but join operates on plain sorted text files via a merge algorithm instead of using database indexes, and has no query planner.
• How would you perform an outer join with join? Use -a 1 to include unmatched lines from the first file, -a 2 for the second, or both together for a full outer join.