Looking for Unix AWK command to perform full outer join of 2 files based on a common column (1st column in unix files is common)

Looking for Unix AWK command to perform full outer join of 2 files based on a common column (1st column in Unix files is common)

1st File - app.txt

5.13.0.12S   Yes
5.13.0.11S   Yes
5.13.0.24S   Yes
5.13.0.23S   Yes
5.13.0.22S   Yes

1st File - db.txt

5.13.0.12S   WLPFO5_13_0_12S   WLPFO   incremental   2023-07-10_11:57:51   Activated   Current
5.13.0.11S   WLPFO5_13_0_11S   WLPFO   full          2023-07-10_06:37:32   Activated   Previous

Tried below AWK command. Did not work well (expecting result like - full outer join)

awk 'FNR==NR {data[$1]=$0; next} {$1=data[$1]}1' db.txt app.txt

Output of AWK -

5.13.0.12S   WLPFO5_13_0_12S   WLPFO   incremental   2023-07-10_11:57:51   Activated   Current Yes
5.13.0.11S   WLPFO5_13_0_11S   WLPFO   full          2023-07-10_06:37:32   Activated   Previous Yes
 Yes
 Yes
 Yes

for last 3 records, expecting Nulls then values (Yes) from app.txt

Please suggest ..
Thanks

Not sure if I've misunderstood, but here is one potential option using AWK:

awk 'FNR == NR {
	num_fields_db = NF
	data[$1] = $0
	next
}

{
	if ($1 in data) {
		print data[$1], $2
	} else {
		for (i = 1; i < num_fields_db; i++) {
			output = output "NULL" "\t"
		}
		print $1 "\t" output $2
		output = ""
	}
}' db.txt app.txt
5.13.0.12S  WLPFO5_13_0_12S  WLPFO  incremental  2023-07-10_11:57:51  Activated  Current   Yes
5.13.0.11S  WLPFO5_13_0_11S  WLPFO  full         2023-07-10_06:37:32  Activated  Previous  Yes
5.13.0.24S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes
5.13.0.23S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes
5.13.0.22S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes

Or with GNU join:

join -e "NULL" -j1 -a1 -o 1.1,2.2,2.3,2.4,2.5,2.6,2.7,1.2 <(sort app.txt) <(sort db.txt)
5.13.0.11S WLPFO5_13_0_11S WLPFO full 2023-07-10_06:37:32 Activated  Previous  Yes
5.13.0.12S WLPFO5_13_0_12S WLPFO incremental 2023-07-10_11:57:51 Activated  Current   Yes
5.13.0.22S NULL NULL NULL NULL NULL NULL Yes
5.13.0.23S NULL NULL NULL NULL NULL NULL Yes
5.13.0.24S NULL NULL NULL NULL NULL NULL Yes

# tab delimited
join -e "NULL" -j1 -a1 -o 1.1,2.2,2.3,2.4,2.5,2.6,2.7,1.2 <(sort app.txt) <(sort db.txt) | tr -s " " "\t"
5.13.0.11S  WLPFO5_13_0_11S  WLPFO  full         2023-07-10_06:37:32  Activated  Previous  Yes
5.13.0.12S  WLPFO5_13_0_12S  WLPFO  incremental  2023-07-10_11:57:51  Activated  Current   Yes
5.13.0.22S  NULL             NULL   NULL         NULL                 NULL       NULL       Yes
5.13.0.23S  NULL             NULL   NULL         NULL                 NULL       NULL       Yes
5.13.0.24S  NULL             NULL   NULL         NULL                 NULL       NULL       Yes

With your shown samples please try following awk solution. Using column to get the output more clear form.

awk '
FNR==1 && NR==1{
  for(i=1;i<=NF;i++){
    null=(null?null "\t":"") "NULL"
  }
}
FNR==NR{
  arr[$1]=$0
  next
}
{
  print ($1 in arr)?arr[$1] "\t" $2:$1 "\t" null
}
' db.txt app.txt | column -t

You may use this awk:

awk '
FNR == NR {
   if (FNR == 1) {
      nulls = sprintf("%*s", NF-1, "");
      gsub(/ /, " NULL", nulls)
   }
   data[$1] = $0
   next
}
{
   print ($1 in data ? data[$1] : $1 nulls), $2
}' db.txt app.txt | column -t

5.13.0.12S  WLPFO5_13_0_12S  WLPFO  incremental  2023-07-10_11:57:51  Activated  Current   Yes
5.13.0.11S  WLPFO5_13_0_11S  WLPFO  full         2023-07-10_06:37:32  Activated  Previous  Yes
5.13.0.24S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes
5.13.0.23S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes
5.13.0.22S  NULL             NULL   NULL         NULL                 NULL       NULL      Yes

Here:

• For the first row in first file, using sprintf, we prepare a string with NF-1 number of spaces in variable nulls first.
• Then using gsub we replace each space with " NULL" in variable nulls
• Store each row of db.txt in associative array data with key as $1
• Finally while processing app.txt we check presence of $1 in associative array data
• If value is found in array then print $1 and mapped value otherwise print variable $1 and nulls
• Use column -t to display output in tabular format.

To do a full outer join using any POSIX awk would be:

$ cat tst.awk
{
    key = $1
    vals = $0
    sub(/[^[:space:]]+[[:space:]]+/,"",vals)
    if ( FNR == 1 ) {
        nomatch[++fileNr] = vals
        gsub(/[^[:space:]]+/,"Null",nomatch[fileNr])
    }
}
NR==FNR {
    file1[key] = vals
    next
}
{
    print $1, ($1 in file1 ? file1[$1] : nomatch[1]), vals
    delete file1[$1]
}
END {
    for ( key in file1 ) {
        print key, file1[key], nomatch[2]
    }
}

To test it modify your example to add lines to db.txt that don't have a shared key in app.txt and add additional fields to app.txt.

It assumes that every line from each file will have the same number of fields as the first line from that file. If that's not true then add whatever logic you want to handle that. If either input file could be empty then you'd need to add logic to define how many fields should be present in the output. It also assumes that the key value is unique in each input file.

You can use join

join -a1 app.txt db.txt

output

5.13.0.12S Yes WLPFO5_13_0_12S WLPFO incremental 2023-07-10_11:57:51 Activated Current
5.13.0.11S Yes WLPFO5_13_0_11S WLPFO full 2023-07-10_06:37:32 Activated Previous
5.13.0.24S Yes
5.13.0.23S Yes
5.13.0.22S Yes