Combine Two Files With Unequal Length On Common Column With Multiple Matches With Linux Command Line

I know some similar questions had been ask, but I struggle to get awk, join or anything else to do what I want. I have to tab delimited files. File 1: Text1 Text2 Text3 1000 128 1

Solution 1:

Using awk

Left Outer Join on file2

$ awk  'FNR==NR{a[$1]=$2FS$3; next} ($1in a){print $0,a[$1]; next} {print $0,"NA","NA"}' file1 file2

Text1Text4Text5Text6Text2Text310001003199010011128128/D5910001002199010012128128/D5910011003199710050116116/A9520001003199710050NANA

FNR==NR{a[$1]=$2FS$3; next} : To store contents of file1 in associative array a where the key is unique field one

($1 in a){print $0,a[$1]}: While iterating over file2 check if the first field/key exists in the array. If yes print its value alongside the record.

If key doesn't exist in array (For eg. 2000) then just print the record which is in file2; this will reflect the behaviour of left outer join on file2.

Inner Join on both files :

$ awk  'FNR==NR{a[$1]=$2FS$3; next} ($1in a){print $0,a[$1]}' file1 file2
Text1Text4Text5Text6Text2Text310001003199010011128128/D5910001002199010012128128/D5910011003199710050116116/A95