Using MySQL UNION Operator

Many operators exist in MySQL to retrieve data from multiple tables based on the requirements. One of the useful MySQL operators is UNION. It is used to combine records from two or more tables by writing a single query. Each select statement used with the UNION operator must contain the same numbers of fields and the data type of each field will be the same also. It retrieves all common and uncommon fields values of all tables mentioned in the query by removing duplicate entries.

Syntax:

Here, the WHERE clause and DISTINCT modifier are optional. If you want to run a select query based on any condition then run the WHERE clause. It is mentioned before that duplicate records are removed automatically when running the query with a UNION operator. So using the DISTINCT modifier is useless.

Prerequisite:

You have to create the necessary database and tables with some records to know the use of the UNION operator. At first, connect with the database server using mysql client and run the following SQL statement to create a database named ‘company’.

Select the current database by executing the following statement.

Run the following SQL statement to create a table named ‘products’ of five fields (id, name, model_no, brand, and price). Here, ‘id‘ is the primary key.

Run the following SQL statement to create a table named ‘suppliers’ of four fields (id, name, address, pro_id). Here, ‘id’ is a primary key and pro_id is a foreign key.

Run the following SQL statement to insert four records into the products table.

Run the following SQL statement to insert six records into the suppliers table.

***Note: It is assumed that the reader is familiar with the SQL statements for creating a database and table or inserting data into tables. So the screenshots of the above statements are omitted.

Run the following SQL statement to see current records of the products table.

Run the following SQL statement to see current records of the suppliers table.

Here, the supplier name ‘Walton Plaza‘ exists in two records. When these two tables are combined with the UNION operator then a duplicate value will be generated but it will be removed automatically by default and you will not require to use a DISTINCT modifier.

Use of a Simple UNION operator

The following query will retrieve the data of pro_id and name fields from suppliers table, and id and name fields from products table.

Here, products table contains 4 records and suppliers table contains 6 records with one duplicate record (‘Walton Plaza’). The above query returns 9 records after removing the duplicate entry. The following image shows the output of the query where ‘Walton Plaza’ appears for one time.

Use of UNION with single WHERE clause

The following example shows the use of the UNION operator between two select queries where the second query contains a WHERE condition to search those records from suppliers table that contains the word, ‘Walton’ in the name field.

Here, The first select query will return 4 records from products table and the second select statement will return 2 records from suppliers table because, the word, ‘Walton’ appears two times in the ‘name’ field. The total 5 records will be returned after removing the duplicate from the result set.

Use of UNION with multiple WHERE clause

The following example shows the use of a UNION operator between two select queries where both queries contain where condition. The first select query contains a WHERE condition that will search those records from products which price values are less than 600. The second select query contains the same WHERE condition as the previous example.

Here, 4 records will be returned as output after removing the duplicates.

Use of UNION ALL with multiple WHERE clause

It is shown in the previous examples that all duplicate records are removed by UNION operators by default. But if you want to retrieve all records without removing duplicates then you have to use UNION ALL operator. The use of UNION ALL operator is shown in the following SQL statement.

The following image shows that the returned result set contains the duplicate records after running the above statement. Here, ‘Walton Plaza’ appears two times.

Conclusion:

The uses of UNION operators in the SQL statement are explained in this tutorial by using simple examples. I hope, the readers will be able to use this operator properly after reading this article.