Union

Union is the process of combining two or more data sets into one. Using select statements, SQL Server's Union function combines two queries into a single result set. All of the rows that the query describes are extracted via union.

Union Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

write your code here: Coding Playground

Example

Consider the following table

Table 1 − CUSTOMERS

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
1 | Rohan   |  42 | Gujarat |  20000.00 |
2 | Katija   |  45 | Mumbai     |  5000.00 |
3 | Krishik  |  32 | Thane      |  20000.00 |
4 | Chitra |  25 | Keer    |  65000.00 |
5 | Hima   |  27 | Bhopal    |  85000.00 |
6 | Prerna    |  29 | Puran        |  45000.00 |
+----+----------+-----+-----------+----------+

Table 2: Orders

+-----+---------------------+-------------+--------+
|O_ID  | DATE      | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 |           3 |   3000 |
| 100 | 2009-10-08 |           3 |   1500 |
| 101 | 2009-11-20 |           2 |   1560 |
| 103 | 2008-05-20 |           4 |   2060 |
+-----+---------------------+-------------+--------+

We will join these two tables using UNION

sql> SELECT  id, name, amount
  from customers
  left join orders
  on customers.id = orders.customer_id
union
  select  id, name, amount
  from customers
  right join orders
  on customers.id = orders.customer_id;

write your code here: Coding Playground

Output

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE        |
+------+----------+--------+---------------------+
|    1 | Rohan   |   NULL | NULL         |
|    2 | Katija   |   1560 | 2009-11-20  |
|    3 | krishik  |   3000 | 2009-10-08  |
|    3 | krishik  |   1500 | 2009-10-08  |
|    4 | Chitra |   2060 | 2008-05-20    |
|    5 | Hima   |   NULL | NULL          |
|    6 | Prerna    |   NULL | NULL      |
+------+----------+--------+---------------------+

Here, left join is performed on tables, customers and orders. Then the right join is performed. Union of both is given as the output.

Union All

To merge the output of two SELECT statements—including duplicate rows—use the UNION ALL operator.The UNION ALL operator will be subject to the same restrictions as the UNION clause.

Syntax

SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
UNION ALL
SELECT column1 [, column2 ] FROM table1 [, table2 ]
[WHERE condition]

write your code here: Coding Playground

Examples

Table 1: Customers

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
1 | Rahul    |  34 | Ahmedabad |  20000.00 |
2 | Kabilan  |  29 | Delhi     |  15000.00 |
3 | kaushik  |  21 | Kota      |  20000.00 |
4 | Cheeta   |  20 | Mumbai    |  65000.00 |
5 | Hamesh   |  25 | Bhopal    |  85000.00 |
+----+----------+-----+-----------+----------+

Table 2: Orders

+-----+---------------------+-------------+--------+
|OID  | DATE       | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 |           3 |   30000 |
| 100 | 2009-10-08 |           3 |   15000 |
| 101 | 2009-11-20 |           2 |   15600 |
| 103 | 2008-05-20 |           4 |   20600 |
+-----+---------------------+-------------+--------+

Joining tables using union all

sql> SELECT  id, name, amount, date
  FROM customers
  left join orders
  ON customers.id = orders.customer_id
UNION ALL

   SELECT  id, name, amount, date
  FROM customers
  right join orders
  ON customers.id = orders.customer_id;

write your code here: Coding Playground

Output

+------+----------+--------+-------------+
| ID   | NAME     | AMOUNT  | DATE       |
+------+----------+--------+-------------+
|    1 | Rahul    |   NULL  | NULL       |
|    2 | Kabilan  |   15600 | 2009-11-20 |
|    3 | kaushik  |   30000 | 2009-10-08 |
|    3 | kaushik  |   15000 | 2009-10-08 |
|    4 | Cheeta   |   20600 | 2008-05-20 |
|    5 | Hamesh   |   NULL  | NULL       |
|    3 | kaushik  |   30000 | 2009-10-08 |
|    3 | kaushik  |   15000 | 2009-10-08 |
|    2 | Kabilan  |   15600 | 2009-11-20 |
|    4 | Cheeta   |   20600 | 2008-05-20 |
+------+----------+--------+-------------+

Conclusion

Union and Union All operators with implementation and examples were discussed. Before returning the final results, UNION performs a deduplication step. UNION ALL keeps all duplicates and returns the complete, concatenated results.