Mysql Union

The UNION set operator is used for combining data from two
tables which have columns with the same datatype.When a UNION is performed the data from both tables will be collected in a single column having the same datatype.

Rules:

• The number of columns appears in the corresponding SELECT statements must be equal.
• The columns appear in  the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.
• You cannot use the union operator on text and image columns.

Notes:

• Union -- returns with no duplicate rows
• Union all -- retruns with duplicate rows (No. of rows returned = No. of rows in Query1 + No. of rows in Query 2)

Example:
Suppose you want to combine data from the  student and teacher tables into a single result set, you can UNION operator as the following query:

SELECT studentNumber id, contactLastname name FROM student
UNION
SELECT teacherNumber id,firstname name FROM teacher

Here is the output:
id name
1  Sudhir
2  Amit
3  Gaurav
4  Himanshu

Order by with UNION

(SELECT studentNumber id,contactLastname name FROM student)
UNION
(SELECT teacherNumber id,firstname name FROM teacher)
ORDER BY name,id

Note: If you place the ORDER BY clause in each SELECT statement,
it will not affect the order of the rows in the final result produced by the UNION operator.