MySQL UNION clause returns combined result sets of multiple MySQL SELECT statements. This is an essential operation when you need result from different SELECT statements (may be from different tables or sources) and you want all the selected rows to be combined to give one set of all the rows or records.
The following applications may require UNION operation
- You get sales related records from multiple data tables storing region wise sales.
- Combining records from new and old tables.
- Selecting common columns from multiple tables and displaying single record-set.
Syntax of MySQL UNION
UNION clause is used to combine output of multiple SELECT statements. It is added between SELECT statements. The SELECT statements can be complete in themselves with all other clauses applicable on individual statements.
SELECT ……UNION [All| DISTINCT ] SELECT…….UNION UNION [All| DISTINCT ] SELECT……
- SELECT statements return the individual result set to be combined.
- UNION is the keyword to combine SELECT statement results
- UNION ALL between two SELECT statements combines the results and it may result in duplicate records after union.
- UNION DISTINCT between two SELECT statements combines the results and displays single record if a record appears in both the SELECT statements. Duplicity in result set is removed
Examples of MySQL UNION
Find list of all artists from old and new tables
The following query displays all employee names and ids from two different tables.
SELECT artist_id,artist_name from artists UNION SELECT artist_id,artist_name from artist_old;
Find list of distinct artist names from old and new tables
The following query displays distinct employee names from two different tables.
SELECT artist_name from artists UNION DISTINCT SELECT artist_name from artist_old;
Using WHERE in Individual Queries
The following query displays distinct employee names from two different tables using WHERE clause in individual queries.
SELECT artist_name FROM artists WHERE country='USA' UNION SELECT artist_name FROM artist_old WHERE country='USA';
Points to remember
- The SELECT queries in must return same columns or columns of same data type.
- WHERE clause is used to filter data from individual SELECT queries.