join
Join two files on a common field, like JOIN in SQL — inner, outer, "unmatched"
join combines lines of two files whose key field values match — like JOIN in SQL. For example, a list of id name and a list of id role become id name role. Both files must be sorted by the key.
Syntax
join [options] file1 file2
join -t, -1 2 -2 1 file1 file2 # key: field 2 of the first and field 1 of the second
One of the files (but not both) can be - — standard input.
Output format
By default, for each pair of lines it prints: the key, then the remaining fields from the first file, then the remaining fields from the second. The output separator is a space (or the character from -t). If a key repeats in both files, all combinations are printed: 2 lines × 2 lines = 4 lines.
Options
Key and separator
| Option | What it does |
|---|---|
-1 N | the key in the first file is field N (default 1) |
-2 N | the key in the second file is field N |
-j N | the same field in both files (-1 N -2 N) |
-t C | input and output field separator — a single character (-t,, -t:). Tab: -t $'\t' |
-i | compare keys case-insensitively |
Without -t, fields are separated by spaces and tabs, leading blanks are ignored, and a single space is used on output.
Which lines to print (JOIN types)
| Option | SQL equivalent | What is printed |
|---|---|---|
| (no options) | INNER JOIN | only lines that have a match |
-a 1 | LEFT JOIN | plus unmatched lines from the first file |
-a 2 | RIGHT JOIN | plus unmatched lines from the second file |
-a 1 -a 2 | FULL JOIN | plus unmatched lines from both |
-v 1 | anti-join | only unmatched lines from the first file |
-v 2 | anti-join | only unmatched lines from the second file |
Line format
| Option | What it does |
|---|---|
-o 1.2,2.3 | print only the listed fields: file.field; 0 — the key |
-o auto | the number of fields is taken from the first line of each file — needed for -e to fill gaps |
-e STRING | what to fill missing fields with (works together with -o) |
--header | the first line of each file is a header: printed once, not used for matching |
Sort order check
| Option | What it does |
|---|---|
--check-order | stop with an error at the first unsorted line |
--nocheck-order | don't check (and don't warn) |
-z | lines are separated by a null byte |
By default join warns about disorder (is not sorted) only if it encountered an unmatched line, and then returns code 1.
Examples
printf '1 alice\n2 bob\n3 carol\n' > users.txt
printf '1 admin\n3 dev\n4 ops\n' > roles.txt
# inner join on the first field
join users.txt roles.txt
# left join: bob without a role is included too
join -a 1 users.txt roles.txt
# full join with a placeholder for gaps
join -a 1 -a 2 -e NULL -o auto users.txt roles.txt
# users without a role / roles without a user
join -v 1 users.txt roles.txt
join -v 2 users.txt roles.txt
# CSV: the key is field 2 in the first file and field 1 in the second, print name and role
printf 'alice,10\nbob,20\n' > people.csv
printf '10,admin\n20,dev\n' > ids.csv
join -t, -1 2 -2 1 -o 1.1,2.2 <(sort -t, -k2,2 people.csv) <(sort -t, -k1,1 ids.csv)
# files with a header
printf 'id name\n1 alice\n2 bob\n' > hu.txt
printf 'id role\n1 admin\n' > hr.txt
join --header -a 1 -e - -o auto hu.txt hr.txt
# sort by the key on the fly
join <(sort -k1,1 users.txt) <(sort -k1,1 roles.txt)
Pitfalls
- Sort both files by the key, as text.
joinexpects the same order thatsort -k1,1produces (orsort -t, -kN,Nfor your field) in the same locale. Numeric sorting won't work: aftersort -nthe order2, 10is out of order forjoin, and lines with key10may not find a match and get lost. - Unsorted input doesn't always give an error. If all lines happen to find a match, there's no warning. If not, some lines disappear and stderr shows
input is not in sorted order. In scripts add--check-orderto fail right away. - A header without
--headercounts as data. It takes part in matching and sorting:sortmoves the lineid nameto the end, after the digits, and the order forjoinbreaks. Either use--headeror cut off the header (tail -n +2) before sorting. -edoesn't work without-o.join -a1 -e NULLprints a short line without the placeholder. Add-o autoor an explicit field list.-tis a single character only. A multi-character separator (::,,) isn't supported — replace it first, e.g. with sed.- Without
-t, alignment by spaces is lost: on output the fields are joined by a single space. For a pretty table pipe the result through column-t. - Case and
\rmatter.Alice≠alice(there's-i), and a key1\rfrom a Windows file won't match1. - Repeated keys multiply. Two lines with key
1in the first file and three in the second produce six lines — check the data for duplicates withuniq -d.