MySQL SELECT UNION – Combining Multiple Tables with Same Schema

MySQLselectunion

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use:

SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2013 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2014 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2015

I am quite certain there is a less redundant and verbose way to write this query. The schema for each of the table is exactly the same. This is in MySQL 5.6

Best Answer

Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job.

But, that may not even be wise. What queries benefit from your splitting it into multiple tables?

Related Solutions

Mysql – How to select using the same fields from an undetermined number of tables using MySQL

You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):

SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
    select CONCAT(table_schema,'.',table_name) tb
    from information_schema.tables
    where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First, let's make some sample tables with sample data:

DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
    foo_id int,
    bar_id int,
    bar_weight int,
    primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);

Here are the sample tables being made and loaded:

mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)

mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)

mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)

mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
    -> (
    ->     foo_id int,
    ->     bar_id int,
    ->     bar_weight int,
    ->     primary key (foo_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

Now let's build the query and execute it:

mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
    -> GROUP_CONCAT(tb SEPARATOR
    -> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
    -> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
    -> INTO @foo_query FROM
    -> (
    ->     select CONCAT(table_schema,'.',table_name) tb
    ->     from information_schema.tables
    ->     where table_name like 'foo_reference%'
    -> ) A;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)

mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
|     27 |         90 |
|     47 |         70 |
|     67 |         50 |
|     37 |         40 |
|     57 |         20 |
+--------+------------+
5 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

CAVEAT

Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.

Also, notice that the subquery will get 6 rows, and the limit 0,5 properly displays the first 5 rows of the subquery.

Give it a Try !!!

MySQL Union – How to Create a Summary with Union

I'm not sure why you are using subqueries in the upper SELECT of the union.

I rewrote your query without the subquery and duplicated the upper and lower SELECTs of the union.

Only then changing the interval on the bottom SELECT to 30.

SELECT 
sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit, 'table1' as TableName
FROM
SAL__DETAIL sd, TESTINVDB ti
WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber
UNION
SELECT 
sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit,'table2' as TableName
FROM
SAL__DETAIL sd, TESTINVDB ti
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber

Here is my work on SQL Fiddle

Best Answer

Related Solutions

Mysql – How to select using the same fields from an undetermined number of tables using MySQL

CAVEAT

MySQL Union – How to Create a Summary with Union

Related Question