join Command in Linux with Examples

Description :

The join command reads the files specified by the File1 and File2 parameters, joins lines in the files according to the flags, and writes the results to standard output. The File1 and File2 parameters must be text files. Both File1 and File2 must be sorted in the collating sequence of sort -b on the field that they are being joined by before invoking the join command.

Usage :

join [OPTION]… FILE1 FILE2

Options :

-a FILENUM
also print unpairable lines from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2
-e EMPTY
replace missing input fields with EMPTY
-i, –ignore-case
ignore differences in case when comparing fields
-j FIELD
equivalent to “-1 FIELD -2 FIELD”.
-o FORMAT
obey FORMAT while constructing output line.
-t CHAR
use CHAR as input and output field separator.
-v FILENUM
like -a FILENUM, but suppress joined output lines.
-1 FIELD
join on this FIELD of file 1.
-2 FIELD
join on this FIELD of file 2.
–check-order
check that the input is correctly sorted, even if all input lines are pairable.
–nocheck-order
do not check that the input is correctly sorted.
–header
treat the first line in each file as field headers, print them without trying to pair them.

Examples :

1. Basic Example

$ cat testfile1
1 India
2 US
3 Ireland
4 UK
5 Canada
 
$ cat testfile2
1 NewDelhi
2 Washington
3 Dublin
4 London
5 Toronto
 
$ join testfile1 testfile2
1 India NewDelhi
2 US Washington
3 Ireland Dublin
4 UK London
5 Canada Toronto

2. Ignore Case using -i option

$ cat testfile1
a India
b US
c Ireland
d UK
e Canada
 
$ cat testfile2
a NewDelhi
B Washington
c Dublin
d London
e Toronto
 
$ join testfile1 testfile2
a India NewDelhi
c Ireland Dublin
d UK London
e Canada Toronto
 
$ join -i testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London
e Canada Toronto

3. Join ONLY works on sorted lists

If any of the two files supplied to join command is not sorted then it shows up a warning in output and that particular entry is not joined.

$ cat testfile1
1 India
2 US
3 Ireland
5 Canada
4 UK
 
$ cat testfile2
1 NewDelhi
2 Washington
3 Dublin
4 London
5 Toronto
 
$ join testfile1 testfile2
1 India NewDelhi
2 US Washington
3 Ireland Dublin
join: testfile1:5: is not sorted: 4 UK
5 Canada Toronto

4. Do not Check the Sortness using –nocheck-order option

This is the opposite of the previous example. No check for sortness is done in this example, and it will not display any error message.

$ join --nocheck-order testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London

5. Verify that Input is Sorted using –check-order option

$ cat testfile1
a India
b US
c Ireland
d UK
f Australia
e Canada
 
$ cat testfile2
a NewDelhi
b Washington
c Dublin
d London
e Toronto
 
$ join --check-order testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London
join: testfile1:6: is not sorted: e Canada

6. Print Unpairable Lines using -a option

If both the input files cannot be mapped one to one then through -a[FILENUM] option we can have those lines that cannot be paired while comparing. FILENUM is the file number (1 or 2).

$ cat testfile1
a India
b US
c Ireland
d UK
e Canada
f Australia
 
$ cat testfile2
a NewDelhi
b Washington
c Dublin
d London
e Toronto
 
$ join testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London
e Canada Toronto
 
$ join -a1 testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London
e Canada Toronto
f Australia

7. Print Only Unpaired Lines using -v option

In the above example both paired and unpaired lines were produced in the output. But, if only unpaired output is desired then use -v option as shown below.

$ join -v1 testfile1 testfile2
f Australia

8. Join Based on Different Columns from Both Files using -1 and -2 option

By default the first columns in both the files is used for comparing before joining. You can change this behavior using -1 and -2 option.

In the following example, the first column of testfile1 was compared with the second column of testfile2 to produce the join command output.

$ cat testfile1
a India
b US
c Ireland
d UK
e Canada
 
$ cat testfile2
NewDelhi a
Washington b
Dublin c
London d
Toronto e
 
$ join -1 1 -2 2 testfile1 testfile2
a India NewDelhi
b US Washington
c Ireland Dublin
d UK London
e Canada Toronto