SQL UNION and UNION ALL set operators combine query results

<<Previous - SQL IN OPERATOR

Next - SQL NULL value >>

• SQL UNION operation is used to combine two or more select quiries result into a single table of the result set.
• Following are the restrictions to use union operation.
  • Each select statement must have the same number of columns.
  • The columns listed in the first select statement and second select statement, both must have the same data type.

Syntax

SELECT Column-name1,Column-name2....Column-nameN FROM Table-name1
UNION SELECT Column-name1,Column-name2....Column-nameN FROM Table-name2;

Note

if both tables have a same record, then this union statement eliminates the duplicate records.

Example

Below is the demo tables from the Collegedb database.

• Table 1:student
  
  
  

  mysql> SELECT * FROM student;
  +--------+---------+------+-----------+
  | RollNo | Name    | Age  | City      |
  +--------+---------+------+-----------+
  |      1 | Aruna   |   18 | Chennai   |
  |      2 | Varun   |   19 | Bangalore |
  |      3 | Ara     |   19 | Kerala    |
  |      4 | Markdin |   18 | Mumbai    |
  |      5 | Kannan  |   20 | Kerala    |
  |      6 | Kanika  |   18 | Chennai   |
  |      7 | Jose    |   19 | Kerala    |
  +--------+---------+------+-----------+
  7 rows in set (1.39 sec)
  

• Table 2: student_table

  mysql> SELECT * FROM student_table;
  +------+---------+------+-----------+
  | SlNo | Name    | Age  | City      |
  +------+---------+------+-----------+
  |  101 | Vendhan |   20 | chennai   |
  |  102 | Varun   |   20 | chennai   |
  |  103 | Mano    |   21 | kerala    |
  |  104 | Jose    |   18 | kerala    |
  |  105 | Boomi   |   19 | Bangalore |
  |  106 | padhri  |   19 | Bangalore |
  |    5 | kannan  |   19 | Bangalore |
  |    6 | kanika  |   19 | Bangalore |
  |    3 | Ara     |   19 | kerala    |
  +------+---------+------+-----------+
  9 rows in set (0.00 sec)
  

Below example query selects the list of records from both student and student_table tables using UNION operator.

mysql> SELECT * FROM student UNION SELECT * FROM student_table;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
|    101 | Vendhan |   20 | chennai   |
|    102 | Varun   |   20 | chennai   |
|    103 | Mano    |   21 | kerala    |
|    104 | Jose    |   18 | kerala    |
|    105 | Boomi   |   19 | Bangalore |
|    106 | padhri  |   19 | Bangalore |
|      5 | kannan  |   19 | Bangalore |
|      6 | kanika  |   19 | Bangalore |
+--------+---------+------+-----------+
15 rows in set (0.35 sec)

UNION ALL

If you want to combine two or more select statements and don't need to delete duplicate records Then you can use UNION ALL operator.

Syntax

SELECT column-name FROM Table-name1
WHERE Condition
UNION ALL
SELECT column-name FROM Table-name2
WHERE  Condition;

Example

Below is the example query statement for union all operator

mysql> SELECT * FROM student UNION ALL SELECT * FROM student_table;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
|    101 | Vendhan |   20 | chennai   |
|    102 | Varun   |   20 | chennai   |
|    103 | Mano    |   21 | kerala    |
|    104 | Jose    |   18 | kerala    |
|    105 | Boomi   |   19 | Bangalore |
|    106 | padhri  |   19 | Bangalore |
|      5 | kannan  |   19 | Bangalore |
|      6 | kanika  |   19 | Bangalore |
|      3 | Ara     |   19 | kerala    |
+--------+---------+------+-----------+
16 rows in set (0.21 sec)

UNION ALL Example with WHERE Clause

mysql> SELECT * FROM student WHERE City='Kerala'
       UNION ALL
       SELECT * FROM student_table WHERE City='kerala' ;
+--------+--------+------+--------+
| RollNo | Name   | Age  | City   |
+--------+--------+------+--------+
|      3 | Ara    |   19 | Kerala |
|      5 | Kannan |   20 | Kerala |
|      7 | Jose   |   19 | Kerala |
|    103 | Mano   |   21 | kerala |
|    104 | Jose   |   18 | kerala |
|      3 | Ara    |   19 | kerala |
+--------+--------+------+--------+
6 rows in set (0.00 sec)

<<Previous - SQL IN OPERATOR

Next - SQL NULL value >>