How to use union in select clause?
I have two tables (created here for example):
X1 Y1
a 1
b 2
c 3
d 4
and
X2 Y2
a 5
m 6
n 7
b 4
And I need an 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 have tried:
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 a sample). After executing this request, I get the error:
Syntax error: next to UNION in select clause
Can someone tell me what is wrong here. Is there any other way to get the output table in the specified format?
source to share
union
used to combine results one by one. You are trying to execute join
results side by side (which you already did!). The only thing you are missing is a call coalesce
to handle 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
source to share
SELECT Coalesce(a.x1,b.x1) 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
You don't need the UNION operator here, union is used to add a result set from a set to a result set from another selection
You just need to use your join here with the correct instruction (which you did right) and get x1 or x2 from one of the tables, since x1 will be equal to x2 within the same row
EDIT: Added coalesce statements to my query to return a value for x if a.x1 doesn't exist but b.x2 exists, also added 0 if field doesn't exist for y1 or y2
source to share