Using awk, how to merge 2 files, say A & B and make a left outer join function and include all columns in both files

Question

I have several files with different number of columns, I need to merge the first file and the second file and make a left outer join in awk corresponding to the first file, and print all the columns in both files corresponding to the first column of both files.

I tried the codes below to get closer to my result. But I cannot type "," where a matching number is not found in the second file. Below is the code. Merging requires sorting and takes longer than awk. My files are large, as are 30 million records.

awk -F ',' '{  
    if (NR==FNR){ r[$1]=$0}
    else{ if($1 in r) 
    r[$1]=r[$1]gensub($1,"",1)}
}END{for(i in r){print r[i]}}' file1 file2

file1

number,column1,column2,..columnN

File2

numbr,column1,column2,..columnN

Output

number,file1.column1,file1.column2,..file1.columnN,file2.column1,file2.column3...,file2.columnN

file1

1,a,b,c
2,a,b,c
3,a,b,c
5,a,b,c

file2

1,x,y
2,x,y
5,x,y
6,x,y
7,x,y

desired output

1,a,b,c,x,y
2,a,b,c,x,y
3,a,b,c,,,
5,a,b,c,x,y

Answer 1

$ cat tst.awk
BEGIN { FS=OFS="," }
NR==FNR {
    tail = gensub(/[^,]*,/,"",1)
    if ( FNR == 1 ) {
        empty = gensub(/[^,]/,"","g",tail)
    }
    file2[$1] = tail
    next
}
{ print $0, ($1 in file2 ? file2[$1] : empty) }

$ awk -f tst.awk file2 file1
1,a,b,c,x,y
2,a,b,c,x,y
3,a,b,c,,
5,a,b,c,x,y

GNU awk gensub(), awks - , [g] sub() .

( !) , , , , :

$ cat tst.awk
BEGIN { FS=OFS="," }
NR==FNR {
    tail = gensub(/[^,]*,/,"",1)
    idx[$1] = NR
    file2[NR] = tail
    if ( FNR == 1 ) {
        file2[""] = gensub(/[^,]/,"","g",tail)
    }
    next
}
{ print $0, file2[idx[$1]] }

$ awk -f tst.awk file2 file1
1,a,b,c,x,y
2,a,b,c,x,y
3,a,b,c,,
5,a,b,c,x,y

, , .

Answer 2

,

awk 'BEGIN{FS=OFS=","}
   FNR==NR{d[$1]=substr($0,index($0,",")+1); next}
   {print $0, ($1 in d?d[$1]:",")}' file2 file1

,

1,a,b,c,x,y
2,a,b,c,x,y
3,a,b,c,,
5,a,b,c,x,y

Answer 3

join :

$ join -t $',' -a 1 -e '' -o 0,1.2,1.3,1.4,2.2,2.3 file1.txt file2.txt

:

-t $',': .

-a 1: 1, 2.

-e '': .

-o: .

file1.txt

1,a,b,c
2,a,b,c
3,a,b,c
5,a,b,c

file2.txt

1,x,y
2,x,y
5,x,y
6,x,y
7,x,y

Output

1,a,b,c,x,y
2,a,b,c,x,y
3,a,b,c,,
5,a,b,c,x,y