LEFT (OUTER) JOIN

Question

My files:

file1.txt
 =========
 key1
 key1
 key1
 key1   
 key2
 key2   
 key3
 key3
 key3
 key4
 key4   

 file2.txt
 =========
 key1   22
 key2   23
 key3   24


 Expected Output :
 ==================
 key1   22
 key1   22
 key1   22
 key1   22    
 key2   23
 key2   23   
 key3   24
 key3   24
 key3   24

All solutions that I found does not duplicate matches strings.

awk '{a[$1]=a[$1]" "$2} END{for(i in a)print i, a[i]}'
join -a 1

What needs to be modified in this approaches to result in a left outer join?

`join file1.txt file2.txt` works for me. – Stéphane Chazelas Mar 01 '18 at 21:49 — Stéphane Chazelas, Mar 01 '18 at 21:49
I have different encodings. Thank you for the answer! – Korolenko S Mar 01 '18 at 21:55 — Korolenko S, Mar 01 '18 at 21:55
The files MUST BE SORTED before JOIN – Korolenko S Mar 01 '18 at 22:40 — Korolenko S, Mar 01 '18 at 22:40

score 2 · Accepted Answer · answered Mar 01 '18 at 21:47

2

Awk solution:

awk 'NR==FNR{ a[$1]=$2; next }$1 in a{ $2=a[$1]; print }' file2.txt file1.txt

The output:

key1 22
key1 22
key1 22
key1 22
key2 23
key2 23
key3 24
key3 24
key3 24

Or simply with join command:

join -o1.1,2.2 file1.txt file2.txt

answered Mar 01 '18 at 21:47

RomanPerekhrest

29,703
3
43
67

LEFT (OUTER) JOIN

1 Answers1