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 tablescustomers 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 andemp_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;