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.

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.

0 comments:

Post a Comment