CODEWITHSUNDEEP
mysqlsqlunion
Ferdinand.kraft
Ferdinand.kraft

Reputation: 12819

UNION ignores column names?

Why does 

select 1 as a, 2 as b
union all
select 20 as b, 10 as a

returns

a   b
1   2
20  10

instead of

a   b
1   2
10  20

?

Is there a way to make union match column names?

Upvotes: 3

Views: 760

Answers (4)

Hart CO
Hart CO

Reputation: 34784

Is there a way to make union match column names?

Nope, selecting the columns in order is required with UNION.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I'm not sure if this solves your problem, but you can use subqueries within the union to put the columns in the "right" order:

(select a, b from (select 1 as a, 2 as b) t)
union all
(select a, b from (select 20 as b, 10 as a) t)

I realize the question is tagged MySQL, which doesn't support full outer join. If it did, you could do do the union all as:

select coalesce(t1.a, t2.a) as a, coalesce(t1.b, t2.b) as b
from (select 1 as a, 2 as b) t1 full outer join
     (select 20 as b, 10 as a) t2
     on 0 = 1;

You can do this in MySQL. This assumes that none of your values are never NULL:

select coalesce(t1.a, t2.a) as a, coalesce(t1.b, t2.b) as b
from (select 1 as a, 2 as b union all select NULL, NULL) t1 join
     (select 20 as b, 10 as a union all select NULL, NULL) t2
     on (t1.a is null or t2.a is null) and coalesce(t1.a, t2.a) is not null

Upvotes: 1

Marc B
Marc B

Reputation: 360762

Union only looks at the number of columns, and their relative positions in the query string. it does NOT mix-match based on aliases or the source column names. e.g. You could have two completely different tables:

SELECT x,y FROM foo
UNION
SELECT p,q FROM bar

What should MySQL do in this case? return a single row

x,y,p,q

because none of the column names match? Nope. That'd be incorrect.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96600

Column names are only pertinent for the first part of the union to deifne the union columns. Other unions will join in the same order the columns are given from the first select and often have differn names. If you want want to relate the first column to the second column, You can't. However you can adjust your second select statment to put the columns in the correct order.

Upvotes: 3

Related Questions