Working with Union in MySQL

UNION IN MySQL :

The UNION is used for combining two or more separate commands to return a single output. The UNION operator is used to combine the result-set of two or more SELECT statements. UNION is useful because we have  same information in multiple tables and we want to fetch rows from all of them at once. we want to select several sets of rows from the same table, but some conditions that characterize each set are not easy to write as a single WHERE clause. UNION allows retrieves  of each set with a simpler WHERE clause in its own SELECT statement. The rows retrieved by each are combined and produced as the final command result.

The unions are useful if you wish to join the results from more than one query, but be aware that the number of the returned columns by each select query should be the same.

Syntax :

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

Example : 

SELECT id, name FROM my_table 
UNION 
SELECT id, name FROM my_table;

UNION ALL :

The UNION ALL operator selects all rows from each table and combine  into a single table. The UNION and UNION ALL is differ from that UNION ALL will not drop duplicate rows and UNION eliminate duplicate rows. it just pulls all rows from all tables fitting our commands specifics and combines them into a table.

Union Joins :

Union Joins allow the results of two commands to be combined into one output result .Union Join is done by having the 2 or more commands together by the UNION operator.

Syntax :

SELECT <fields>
 FROM <table>
 WHERE <condition>
UNION
SELECT <fields>
 FROM <table>
 WHERE <condition>

Example : In this example shows the combination of two queries with one result set. Such as

SELECT first_name
FROM employee
WHERE (first_name LIKE 'J%')
UNION
SELECT first_name
FROM employee
WHERE (first_name LIKE 'R%');






Example : in the following example describes two where conditions.

SELECT first_name
FROM employee
WHERE ((first_name LIKE 'J%') || (first_name LIKE 'R%'));




Combining Result Sets with MySQL UNION

MySQL UNION statement to combine two or more result sets from multiple SQL SELECT statements into a single result set. Union allow we to combine two or more result set from multiple tables together

Synatx :

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement
UNION [DISTINCT | ALL]

 

Example : In this example shows that the combination of two tables customers and emp_info and return into one result set.

Query :

SELECT id, Last_name
FROM employee
UNION
SELECT id,first_name
FROM emp_info;

Example : To use an Order by clause  sort entire union .For example if we want to sort the combination of employee and emp_info in the query above by last_name and ID in ascending order.

Query :

(SELECT id, Last_name
FROM employee)
UNION
(SELECT  id,first_name  
FROM emp_info)
ORDER BY last_name,id;