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.