CODEWITHSUNDEEP
mysqlunion
Shl
Shl

Reputation: 3688

MySQL union returns mixed columns

Can someone please explain to me why this is happening?

I have this script:

(select * from (select * from a where a_in > 2) t1 LEFT join b using (sid)) 
union 
(select * from a right join (select * from b where b_in > 2) t2 using (sid));

the tables are:

a
sid name    a_in
1   hello   2
2   me      3
3   hhh     3

b
id sid b_in
1   1   3
2   2   3
3   3   2

CREATE TABLE `a` (
  `sid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `a_in` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(11) DEFAULT NULL,
  `b_in` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

The result, for some reason is mixed: 

sid name    a_in    id      b_in
2   me      3       2       3
3   hhh     3       3       2
1   1       3       hello   2
2   2       3       me      3

the expected result should have been:

sid name    a_in    id      b_in
2   me      3       2       3
3   hhh     3       3       2
1   hello   2       1       3

So, the question is - why the columns are mixed??

Upvotes: 0

Views: 345

Answers (2)

EchoMike444
EchoMike444

Reputation: 1692

Can you try this ?

(select t1.*,b.* from (select * from a where a_in > 2) t1 LEFT join b using (sid)) 
union 
(select a.*,t2.* from a right join (select * from b where b_in > 2) t2 using (sid))

Upvotes: 1

Shl
Shl

Reputation: 3688

So, now I realise that 'Union' don't bother searching and matching the fields from the different queries, and instead - expects the order of the field list to be the same as the first query. And because I didn't provide a field list implicitly (I used *) - it took some "random" order for each query, and the orders didn't match.

So, the first order that was taken automatically was sid, name, a_in, id, b_in. while the second query order (that was also taken automatically) was: sid, id, a_in, name, b_in.

Upvotes: 1

Related Questions