CODEWITHSUNDEEP
mysqlunion
Rasly Walker
Rasly Walker

Reputation: 61

mysql union wrong columns in result

I am getting wrong column names when using union.

Here is what i do, i have two very big tables with same structure and different records, so here it is.

mysql> select * from e18 where `15` like '%car%' limit 1;
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| id   | 1    | 2  | 3    | 4    | 5    | 6    | 7    | 8    | 9    | 10   | 11   | 12   | 13   | 14   | 15          | 16   | 17   | 18   | 19   | 20   | 21   | 22     | 23   | 24   | 25   | 26   | 27   | 28   | 29   | 30   | 31   | 32   | 33   | 34   | 35   | 36   | 37   | 38        |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| 2730 | 2730 | 18 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | cars: stuff | NULL | NULL | NULL | NULL | NULL | NULL |  5  1  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | yy        |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
1 row in set

mysql> (select * from e8 where `15` like '%car%') union
(select * from e10 where `15` like '%car%') union
(select * from e18 where `15` like '%car%') limit 1;");
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| id   | 1    | 2  | 3    | 4    | 5    | 6    | 7    | 8    | 9    | 10   | 11   | 12   | 13   | 14   | 16   | 17          | 18   | 19   | 20   | 21   | 22   | 23   | 24   | 25     | 26   | 27   | 28   | 29   | 30   | 31   | 32   | 33   | 34   | 35   | 36   | 37   | 38   | 15        |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
| 2730 | 2730 | 18 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | cars: stuff | NULL | NULL | NULL | NULL | NULL | NULL | NULL |  5  1  | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | yy        |
+------+------+----+------+------+------+------+------+------+------+------+------+------+------+------+------+-------------+------+------+------+------+------+------+------+--------+------+------+------+------+------+------+------+------+------+------+------+------+------+-----------+
1 row in set

Union all and union return same result in this case.

There is only one row with word part "car" in it and it is in table e18.

For some reason, column names in result that i get from using usion are messed up, looks like i am missing something, any ideas what it is ?

Thanks in advance.

Upvotes: 3

Views: 2267

Answers (2)

Ariel
Ariel

Reputation: 26753

Union works by column position NOT name. But you have not specified the column position because you did * so it's in some order picked by the database, but not picked by you.

The name of the final result set is the name of the columns in the first query in the union.

The fix is easy: Write out the names of all the columns you want, and make sure to keep the order consistent between all three queries.

The columns are NOT sorted by name (so renaming the columns will not help you), the order is some internal order in the database.

Using * is considered poor practice: You don't know what you are getting, and if you only need some of the columns then using * retrieves more data then necessary, making things slower.

BTW Naming columns like this (by number) is very poor programming practice. How in the world do you keep things straight? Your columns have numbers, your tables have numbers. Are you trying to write obfuscated code? To make sure no one else can ever work on your code? Because if you are, this is one way to do it.

Upvotes: 5

dnagirl
dnagirl

Reputation: 20456

It appears that in the first query your field 15 is in numerical order with the other fields. In the 2nd query, it's showing up at the end. If you specify the fields you want (yes I know it's a lot of typing), then you won't have this problem.

Secondarily, given the number of NULLs in your return set and the fact that you're using multiple tables to store the same sort of data, your data probably isn't well normalized. Your database will be much easier to use (as well as faster and more efficient) if you normalize it.

And thirdly, 15 is not a reasonable field name -especially not when sibling fields are named for other numbers.

Upvotes: 0

Related Questions