How to combine MySQL queries with different column values?
Definitions:
- In the results, * denotes an empty column
- The data in the tables is such that each field in the table has a Fieldname + RowCount value (so column "a" in row 1 contains the value "a1").
2 MySQL tables
-
Table1 Field names: a, b, c, d
-
Table2 Field names: e, f, g, h, i, j
A task:
I want to get the first 4 rows from each table.
Offline queries
SELECT Table1.* FROM Table1 WHERE 1 LIMIT 0,4 -- Colcount 4
SELECT Table2.* FROM Table2 WHERE 1 LIMIT 0,4 -- Colcount 6
Simple UNION queries fail because the two parts have different columns.
Version1: add two blank fields to the first request
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 table 2 are redefined in table 1.
Version2 - shift columns 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 solved, but I am getting a lot of empty fields.
Is there a known performance issue?
How do you solve this problem?
Is there a best practice for solving this problem?
source to share
The query result should be a table that is a set of rows, each row with the same set of column names and types. (There are some DBMSs that support torn rows - with different sets of columns, but this is not the main function.)
You need to decide how to handle two sets of four rows with different sets of columns in two sets.
The simplest option is usually to run two offline queries. The two result sets are not comparable and should not be combined.
If you choose your version 1, you must 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 end columns of the first UNION clause; at the moment everyone has 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
(AS comments in the second are redundant but self-consistent, the two halves of the UNION have the same column headings).
Except that you supplied empty strings instead of NULL, the notation you choose is "OUTER UNION". You can find references to it in separate parts of the literature (EF Codd in the book RM / V2, CJ Date in criticizing all things OUTER). SQL 1999 provided it as a UNION JOIN; SQL 2003 removed UNION JOIN (which is pretty unusual - and function bane).
I would use two separate queries.
source to share