Wednesday, 17 June 2015

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.


  • 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.


  • 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)

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
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)
(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.


Post a Comment