14 - MySQL Union

Union in MySQL is used to combine the result set of the query on a single table or on the multiple tables. Both the select statements must have the same number of columns in their result sets although there name can be different. Basic syntax of the UNION command is:

SELECT columnName(s) FROM tableName1
UNION
SELECT columnName(s) FROM tableName2;

In UNION command, the data types of the corresponding columns on both queries must be same orconvertible.

Suppose you the two tables of students from the different classes and you want to have a result set of all student irrespective of their class i.e. from both the tables then the UNION command will do so. For example:

ID

Name

Address

Class

1

Aman

Faridabad

Computers

2

John

Delhi

Computers

3

Salman

Jaipur

Computers

4

Manish

Kerala

Computers

                 Computer class table

ID

Name

Address

Class

12

Segar

Calcutta

Electronics

21

Murphy

Grdao

Electronics

35

Richards

Keral

Electronics

42

Gray

Shimla

Electronics

Electronics Class table

The UNION command to get the list of all the tables from these classes is as:

SELECT ID, Name, Address, Class from Computer_class
UNION
SELECT ID, Name, Address, Class from Electronics_class ;                       

The result of the union query is combination of both tables result as:

IDNameAddressClass
1AmanFaridabadComputers
2JohnDelhiComputers
3SalmanJaipurComputers
4ManishKeralaComputers
12SegarCalcuttaElectronics
21MurphyGrdaoElectronics
35RichardsKeralElectronics
42GrayShimlaElectronics

Note: By Default UNION query gives the DISTINCT result, removes the duplicate rows automatically from the final result set. Although it providesthe keyword “ALL” to show all duplicate in the result set also. The basic syntax of UNION ALL is simple as:The result set of multiple tables shown in the order of queries written in union command.

SELECT columnName(s) FROM tableName1
UNION ALL
SELECT columnName(s) FROM tableName2;

Like us on Facebook