UNION

---

UNION in SQL server

The UNION in SQL server is used to combine the result-set of two or more SELECT statements.

In order to execute UNION, it must possess following rules:

1. Each SELECT statement within UNION must have the same number of columns

2. The columns must also have similar data types

3.The columns in each SELECT statement must also be in the same order

UNION Syntax

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

UNION

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

Here, the given condition could be of any given expression based on our requirement.

Example

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |
|  3 | kaulik  |  23 | Kota      |  2000.00 |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |
|  5 | Munny   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows

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

Now, let us join these two tables using UNION in our SELECT statement as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   LEFT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
   SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   RIGHT JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|  1 | Ranith    |   NULL  | NULL                |
|  2 | Krupa     |   1560  | 2009-11-20 00:00:00 |
|  3 | kaulik    |   3000  | 2009-10-08 00:00:00 |
|  3 | kaulik    |   1500  | 2009-10-08 00:00:00 |
|  4 | Chaitan   |   2060  | 2008-05-20 00:00:00 |
|  5 | Munny     |  NULL   | NULL                |
+----+-----------+---------+---------------------+   

UNION ALL

The UNION ALL helps to combine the results of two SELECT statements including duplicate rows.Same rules of UNION clause will apply to the UNION ALL as well.

Syntax

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

UNION ALL

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

Example

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |
|  3 | kaulik  |  23 | Kota      |  2000.00 |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |
|  5 | Munny   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS table is as follows.

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

Now, let us join these two tables using UNION ALL in our SELECT statement as follows:

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;

This would produce the following result:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|  1 | Ranith    |   NULL  | NULL                |
|  2 | Krupa     |   1560  | 2009-11-20 00:00:00 |
|  3 | kaulik    |   3000  | 2009-10-08 00:00:00 |
|  3 | kaulik    |   1500  | 2009-10-08 00:00:00 |
|  4 | Chaitan   |   2060  | 2008-05-20 00:00:00 |
|  5 | Munny     |   NULL  | NULL                |
|  3 | kaulik    |   3000  | 2009-10-08 00:00:00 |
|  3 | kaulik    |   1500  | 2009-10-08 00:00:00 |
|  2 | Krupa     |   1560  | 2009-11-20 00:00:00 |
|  4 | Chaitan   |   2060  | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+