UNION 2 tables lots fields, few different, possible use SELECT *?

I have 2 MYSQL tables, one with 794 fields, another with 796 fields, the common 794 fields the same. Yes genuine figures, importing data from old system to new one.

If I do:

SELECT foo 
FROM (SELECT * 
      FROM bar1 UNION 
      SELECT *, null as `Extra Field 1`, null as `Extra Field 2` 
      FROM bar2
     ) bar1_2 
WHERE key = "Value in bar2 only";

this gives the wrong value of foo.

Where SELECT foo FROM bar2 WHERE key = "Value in bar2 only";

gives the correct value of foo

i.e. the columns have been shifted, even though Extra Field 1 and Extra Field 2 are the correct names of the extra fields.

Is there a way to still use * or some other shortcut, to avoid writing the names of 796 columns in the query?

I could programatically build up the query string, but in case there’s a SQL-only way?

Answer

You can’t use UNION with different number of columns without writing the column names explicitly.

Therefore if you want to avoid writing the column names then you can add two extra fields in bar1 and then execute your UNION query.

Advertisement

Answer