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?

+3


source to share


4 answers


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

      

+8


source


You can try COALESCE

The function COALESCE

returns the first of its arguments, which is not zero. Null is returned only if all arguments are zero.



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

      

+4


source


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

+3


source


The error is that UNION is not a command that can be used in the list of columns, it is at a given level, you can choose UNION of two types:

SELECT * FROM table1
UNION
SELECT * FROM table2

      

they just need to have the same columns

+1


source







All Articles