MySQL UNION

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;
MySQL Union

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;
UNION Distinct

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';
UNION Where

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.