CODEWITHSUNDEEP
sqlpostgresqlselectunion
Madhusudan
Madhusudan

Reputation: 4815

How to use union in select clause?

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?

Upvotes: 4

Views: 2620

Answers (4)

Adriaan Stander
Adriaan Stander

Reputation: 166606

You can try COALESCE

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.

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

Upvotes: 4

Jeremy C.
Jeremy C.

Reputation: 2465

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 statement here, the union is used to add a resultset from a select to a resultset from a different select

You just need to use your join here with the correct on statement (which you did correct) and get the x1 or x2 from one of the tables as x1 would be equal to x2 within the same row

EDIT: Added coalesce statements to my query to return value for x if a.x1 does not exist but b.x2 does exist, also added 0 if a field doesn't exist for y1 or y2

Upvotes: 3

Mureinik
Mureinik

Reputation: 312259

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

Upvotes: 9

Hristo Bojkov
Hristo Bojkov

Reputation: 85

The error is because UNION is not command that can be used in the list of columns, it is on set level, you can UNION two selects like:

SELECT * FROM table1
UNION
SELECT * FROM table2

they just need to have same columns

Upvotes: 1

Related Questions