There are several identical tables in the database. How to make a request from all? Here is a request to choose from one:

mysql_query("select * from `table` where `dt` = '20161201'");
  • one
    use UNION or UNION ALL apparently - Alexey Shimansky
  • one
    Clarify the question. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. - Pyramidhead
  • Thank you. An option. - Andrei

2 answers 2

UNION - used to combine the results of two or more SQL queries into a single table consisting of similar rows. Both queries must return the same number of columns and compatible data types in the corresponding columns.

Example

 <запрос1> UNION [ALL] <запрос2> UNION [ALL] <запрос3> .....;

More detailed example

 SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

You can write UNION or UNION ALL .

The difference between the UNION and UNION ALL is that the first one throws out duplicate lines from the final result.

Addition from @teran from the comment: in a separate column, you can choose, for example, the name of the table from which the data came. a la

 ...., 'customers' as tbl FROM Customers

if you suddenly need to know where the lines actually came from (which will make sense only in UNION ALL )

  • "two or more" is better corrected. Alternatively, in a separate column, you can select, for example, the name of the table from which the data came. a la ...., 'customers' as tbl FROM Customers , if you suddenly need to know where the lines actually came from (which will make sense only in the case of union all ) - teran

Create an over table using ENGINE = Merge. As a last resort uniting VIEW. Just at the bottom - UNION ALL directly in the request.

If you use just UNION (or its synonym UNION DISTINCT) in VIEW or direct request - get ready for a powerful overhead to sort and delete duplicates (well, that is, it will be slowed down).

  • Merge is for myISAM ...... and if InnoDB? If requests are c * then overhead anyway) - Alexey Shimansky
  • If InnoDB, then someone is not lucky. But where does the overhead with UNION ALL come from, if specific fields are not indicated and I did not understand something without adding the identifier field of a specific table. - Akina