MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL UNION

From WikiOD

Syntax[edit | edit source]

  • UNION DISTINCT -- dedups after combining the SELECTs
  • UNION ALL -- non dedup (faster)
  • UNION -- the default is DISTINCT
  • SELECT ... UNION SELECT ... -- is OK, but ambiguous on ORDER BY
  • ( SELECT ... ) UNION ( SELECT ... ) ORDER BY ... -- resolves the ambiguity

Remarks[edit | edit source]

UNION does not use multiple CPUs.

UNION always* involves a temp table to collect the results. *As of 5.7.3 / MariaDB 10.1, some forms of UNION deliver the results without using a tmp table (hence, faster).

Combining SELECT statements with UNION[edit | edit source]

You can combine the results of two identically structured queries with the UNION keyword.

For example, if you wanted a list of all contact info from two separate tables, authors and editors, for instance, you could use the UNION keyword like so:

select name, email, phone_number 
from authors

union

select name, email, phone_number
from editors

Using union by itself will strip out duplicates. If you needed to keep duplicates in your query, you could use the ALL keyword like so: UNION ALL.

ORDER BY[edit | edit source]

If you need to sort the results of a UNION, use this pattern:

( SELECT ... )
UNION
( SELECT ... )
ORDER BY

Without the parentheses, the final ORDER BY would belong to the last SELECT.

Pagination via OFFSET[edit | edit source]

When adding a LIMIT to a UNION, this is the pattern to use:

( SELECT ... ORDER BY x  LIMIT 10 )
UNION
( SELECT ... ORDER BY x  LIMIT 10 )
ORDER BY x  LIMIT 10

Since you cannot predict which SELECT(s) will the "10" will come from, you need to get 10 from each, then further whittle down the list, repeating both the ORDER BY and LIMIT.

For the 4th page of 10 items, this pattern is needed:

( SELECT ... ORDER BY x  LIMIT 40 )
UNION
( SELECT ... ORDER BY x  LIMIT 40 )
ORDER BY x  LIMIT 30, 10

That is, collect 4 page's worth in each SELECT, then do the OFFSET in the UNION.

Combining data with different columns[edit | edit source]

SELECT name, caption as title, year, pages FROM books 
UNION
SELECT name, title, year, 0 as pages FROM movies

When combining 2 record sets with different columns then emulate the missing ones with default values.

UNION ALL and UNION[edit | edit source]

SELECT 1,22,44 UNION SELECT 2,33,55

UNION

SELECT 1,22,44 UNION SELECT 2,33,55 UNION SELECT 2,33,55

The result is the same as above.

use UNION ALL

when

SELECT 1,22,44 UNION SELECT 2,33,55 UNION ALL SELECT 2,33,55

UNION

Combining and merging data on different MySQL tables with the same columns into unique rows and running query[edit | edit source]

This UNION ALL combines data from multiple tables and serve as a table name alias to use for your queries:

SELECT YEAR(date_time_column), MONTH(date_time_column), MIN(DATE(date_time_column)), MAX(DATE(date_time_column)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM (
    (SELECT date_time_column, ip FROM server_log_1 WHERE state = 'action' AND log_id = 150) UNION ALL
    (SELECT date_time_column, ip FROM server_log_2 WHERE state = 'action' AND log_id = 150) UNION ALL
    (SELECT date_time_column, ip FROM server_log_3 WHERE state = 'action' AND log_id = 150) UNION ALL
    (SELECT date_time_column, ip FROM server_log WHERE state = 'action' AND log_id = 150)
) AS table_all
GROUP BY YEAR(date_time_column), MONTH(date_time_column);

Credit:Stack_Overflow_Documentation