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; |
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; |
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]
|
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; |
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.