Full outer join like functionality but merging columns

Question

TableA

id  | symbolA
 1       m
 2       n
 4       o

TableB

id  | symbolB
 2       p
 3       q
 5       r

I'd like the following result:

id    |  symbolA     | symbolB
2          n              p
1          m              NULL
4          o              NULL
3          NULL           q
5          NULL           r

This is what I've already tried:

SELECT
  TableA.id,
  TableB.id,
  TableA.symbolA,
  TableB.symbolB
FROM
  TableA
FULL OUTER JOIN
  TableB
ON
  TableA.id = TableB.id


NewTable
TableA.id | TableA.symbolA | TableB.id | TableA.symbolB
    2             n             2               p
    1             m             NULL            NULL
    4             o             NULL            NULL
    NULL       NULL             3               q
    NULL       NULL             5               r

I've already tried a full outer join in combination with a coalesce but it doesn't exactly give me the the above desired output. The language is BigQuery, though I'm hoping there's nothing idiosyncratic about my request that wouldn't make it SQL language agnostic. Thanks.

Please show us your current attempt, the output it produces, and explain us how it is not exactly what you want. — GMB, Feb 17 '20 at 21:37
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) [ask] For SQL that includes version & DDL, which includes constraints & indexes & tabular initialization. Isolate the first unexpected/misunderstood subexpression & its input & output. (Debugging fundamental.) Ask re that. — philipxy, Feb 17 '20 at 21:37

score 3 · Answer 1 · answered Feb 17 '20 at 21:53

You are almost there. You just need to merge the two id columns.

For this, you can use coalesce():

select
    coalesce(a.id, b.id) id,
    a.symbola,
    b.symbolb
from tablea a
full outer join tableb b on a.id = b.id

The following syntax that joins with using should also work (most databases that support full join allow this):

select
    id,
    a.symbola,
    b.symbolb
from tablea a
full outer join tableb b using(id)

Full outer join like functionality but merging columns

1 Answers1