How to merge MySQL queries with different column counts?

Question

Definitions:

• In the results, * denotes an empty column
• The data in the tables is such that every field in the table has the value Fieldname + RowCount (so column 'a' in row 1 contains the value 'a1').

2 MySQL Tables

• Table1 Fieldnames: a,b,c,d

• Table2 Fieldnames: e,f,g,h,i,j

Task:

I want to get the first 4 rows from each of the tables.

Standalone Queries

SELECT Table1.* FROM Table1 WHERE 1 LIMIT 0,4 -- Colcount 4
SELECT Table2.* FROM Table2 WHERE 1 LIMIT 0,4 -- Colcount 6

A simple UNION of the queries fails because the two parts have different column counts.

Version1: add two empty fields to the first query

SELECT Table1.*,'' AS i,'' AS j FROM Table1 WHERE 1 LIMIT 0,4  
UNION 
SELECT Table2.* FROM Table2 WHERE 1 LIMIT 0,4

So I will get the following fields in the result set:

a,b,c,d,i,j

a1,b1,c1,d1,*,*,
a2,b2,c2,d2,*,*,
....
....
e1,f1,g1,h1,i1,j1
e2,f2,g2,h2,i2,j2

The problem is that the field names of Table2 are overridden by Table1.

Version2 - shift columns by using empty fields:

  SELECT Table1.*,'','','','','','' FROM Table1 WHERE 1 LIMIT 0,4  
  UNION 
  SELECT '','','','',Table2.* FROM Table2 WHERE 1 LIMIT 0,4

So I will get the following fields in the result set:

a,b,c,d,i,j

a1,b1,c1,d1,*,*,*,*,*,*,
a2,b2,c2,d2,*,*,*,*,*,*,
....
....
*,*,*,*,e1,f1,g1,h1,i1,j1
*,*,*,*,e2,f2,g2,h2,i2,j2
....
....

Problem is solved but I get many empty fields.

Is there a known performance issue?

How do you solve this task?

Is there a best practice to solve this issue?

Answer 1

The thing that seems most sensible is your "version 2", except using NULLs instead of empty strings.

Answer 2

The output from a query should be a table, which is a set of rows, each row with the same set of column names and types. (There are some DBMS that support ragged rows - with different sets of columns, but that is not a mainstream feature.)

You have to decide how to handle two sets of four rows with different sets of columns in the two sets.

The simplest option, usually, is to do the two standalone queries. The two result sets are not comparable, and should not be conflated.

If you choose your Version 1, then you should decide which set of column names is appropriate, or create a composite set of names using 'AS x' column aliases.

If you choose your Version 2, then you should probably name the trailing columns of the first clause of the UNION; at the moment, they all have no name:

SELECT Table1.*, '' AS e, '' AS f, '' AS g, '' AS h, '' AS i, '' AS j
  FROM Table1 WHERE 1 LIMIT 0,4  
UNION 
SELECT '' AS a, '' AS b, '' AS c, '' AS d, Table2.*
  FROM Table2 WHERE 1 LIMIT 0,4

(The AS comments in the second are redundant, but self-consistent; the two halves of the UNION have the same column headings explicitly.)

Except that you have provided empty strings instead of NULL, the notation you have chosen corresponds to an 'OUTER UNION'. You can find occasional references to it in selected parts of the literature (E F Codd in the RM/V2 book; C J Date in critiques of all things OUTER). SQL 1999 provided it as a UNION JOIN; SQL 2003 removed UNION JOIN (that's pretty unusual - and damning of the feature).

I'd use two separate queries.