SQL Unions Clause
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.
To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.
Syntax:
The basic syntax of UNION is as follows:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based on your requirement.
Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS as follows:
CHAPTER 25
27
+-----+---------------------+-------------+--------+
|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 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 | Ramakrishna | NULL | NULL |
| 2 | Mdhu sudhan rao | 1560 | 2009-11-20 00:00:00 |
| 3 | Sai | 3000 | 2009-10-08 00:00:00 |
| 3 | Sai | 1500 | 2009-10-08 00:00:00 |
| 4 | Kishor | 2060 | 2008-05-20 00:00:00 |
| 5 | Ranjith | NULL | NULL |
| 6 | Abhi | NULL | NULL |
| 7 | Kumar | NULL | NULL |
+------+----------+--------+---------------------+
The UNION ALL Clause:
The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.
The same rules that apply to UNION apply to the UNION ALL operator.
Syntax:
The basic syntax of UNION ALL is as follows:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based on your requirement.
Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS 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 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 | Ramakrishna | NULL | NULL |
| 2 | Mdhu sudhan rao | 1560 | 2009-11-20 00:00:00 |
| 3 | Sai | 3000 | 2009-10-08 00:00:00 |
| 3 | Sai | 1500 | 2009-10-08 00:00:00 |
| 4 | Kishor | 2060 | 2008-05-20 00:00:00 |
| 5 | Ranjith | NULL | NULL |
| 6 | Abhi | NULL | NULL |
| 7 | Kumar | NULL | NULL |
| 3 | Sai | 3000 | 2009-10-08 00:00:00 |
| 3 | Sai | 1500 | 2009-10-08 00:00:00 |
| 2 | Mdhu sudhan rao | 1560 | 2009-11-20 00:00:00 |
| 4 | Kishor | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
There are two other clauses (i.e., operators), which are very similar to UNION clause:
• SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
• SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.
INTERSECT Clause
The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.
Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL does not support INTERSECT operator
Syntax:
The basic syntax of INTERSECT is as follows:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based on your requirement.
Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS 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 in our SELECT statement as follows:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INTERSECT
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 |
+------+---------+--------+---------------------+
| 3 | Sai | 3000 | 2009-10-08 00:00:00 |
| 3 | Sai | 1500 | 2009-10-08 00:00:00 |
| 2 | Ramakrishna | 1560 | 2009-11-20 00:00:00 |
| 4 | Sai | 2060 | 2008-05-20 00:00:00 |
+------+---------+--------+---------------------+
EXCEPT Clause
The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in second SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator. MySQL does not support EXCEPT operator.
Syntax:
The basic syntax of EXCEPT is as follows:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here given condition could be any given expression based on your requirement.
Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b) Another table is ORDERS 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 in our SELECT statement as follows:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
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 | Ramakrishna | NULL | NULL |
| 5 | Ranjith | NULL | NULL |
| 6 | Abhi | NULL | NULL |
| 7 | Kumar | NULL | NULL |
+----+---------+--------+---------------------+
