How to use union in select clause?

Question

I have two tables (created here for example) like:

X1   Y1
a   1
b   2
c   3
d   4 

and

X2   Y2
a    5
m    6
n    7
b    4

And I want the output column:

X    Y1   y2
a    1    5
b    2    4
c    3    0
d    4    0
m    0    6
n    0    7

What I tried is:

SELECT (A.X1 UNION B.X1) AS X, A.Y1,B.Y2
FROM A FULL OUTER JOIN B
ON A.X1 = B.X2

(the query mentioned above is just sample). After executing this query I am getting error message:

Syntax error: near UNION in select clause

Can someone tell me what is wrong here. Is there any other option to get the output table in the mentioned format?

Answer 1

union is used to join results one after another. You're attempting to join results side by side (which you already did!). The only thing you're missing is a coalesce call to handle the missing values:

SELECT COALESCE(a.x1, b.x2) AS x, 
       COALESCE(a.y1, 0) AS y1, 
       COALESCE(b.y2, 0) AS y2
FROM   a 
FULL OUTER JOIN b on a.x1 = b.x2