UNION

Syntax

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

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).

Found a mistake? Have a question or improvement idea? Let me know.

UNION:

* UNION

* Combining SELECT statements with UNION

* ORDER BY

* Pagination via OFFSET

* Combining data with different columns

* UNION ALL and UNION

* Combining and merging data on different MySQL tables with the same columns into unique rows and running query

Table Of Contents

0 Getting started with MySQL

1 Limit and Offset

2 Creating Databases

3 Backup using mysqldump

4 Table Creation

5 Insert

6 Error Codes

7 Stored routines, procedures and functions

8 Reserved Words

9 String operations

10 Delete

11 View

12 Indexes and Keys

13 Date and Time Operations

14 Comments

15 LOAD DATA INFILE

16 PREPARE Statements

17 ALTER TABLE

18 Joins

19 UPDATE

20 Change Password

21 JSON

22 MySQL Admin

23 TRIGGERS

24 Pivot Queries

25 Configuration and tuning

26 Converting from MyISAM to InnoDB

27 SELECT

28 Create New User

29 Group By

30 UNION

31 Drop Table

32 Data Types

33 Performance Tuning

34 Events

35 ENUM

36 Install MySQL with Docker

37 Arithmetic

38 Character Sets and Collations

39 MyISAM Engine

40 Many-to-many Mapping table

41 Using Variables

42 Log files

43 Partitioning

44 Security via GRANTs

45 Backticks

46 mysqlimport

47 MySQL LOCK TABLE

48 MySQL Unions

49 ORDER BY

50 MySQL client

51 MySQL Performance Tips

52 Temporary Tables

53 Transaction

54 Customize PS1

55 Dealing with sparse or missing data

56 Dynamic Un-Pivot Table using Prepared Statement

57 NULL

58 Replication

59 Connecting with UTF-8

60 SSL Connection Setup

61 Handling Time Zones

62 Time with subsecond precision

63 Error 1055 ONLY FULL GROUP BY

64 Full-Text search

65 Extract values from JSON type

66 Regular Expressions

67 Recover and reset default root password

68 One to Many

69 JOINS Join 3 tables

70 Server Information

71 Recover from lost root password

72 Contributors