UNIQUE ALL Clause दो या दो से अधिक SELECT Statements के Result Sets को जोड़ने के लिये उपयोग किया जाता है.
UNIQUE ALL Clause Various SELECT Statements के बीच Duplicate Rows को Remove नहीं करता है बल्कि यह सभी Rows को Return कर देता है.
Union ALL Clause के भीतर प्रत्येक SELECT Statements के समान Data Types के साथ Result Sets मे Fields के समान Number होने चाहिए.
UNION जो कि Duplicate Rows को Remove कर देता है और UNION ALL duplicate Rows को Remove नहीं करता है.
UNION Clause BLOB और CLOB Data Type Columns को Support नहीं करता है, जहां UNION ALL Clause सभी Data Types को Support करता है.
निम्नलिखित Product ध्यान में रखते हुए out_of_stock_product हमारी Example Table है.
Table मे एक ही Column Name product_name है Table को बनाने के लिए इसे Execute करते है.
CREATE TABLE product(
category_id number(3),
product_name VARCHAR(25)
);
INSERT INTO product VALUES (1,'Nokia');
INSERT INTO product VALUES (2,'Samsung');
INSERT INTO product VALUES (3,'BlackBerry');
INSERT INTO product VALUES (4,'Microsoft Lumia');
INSERT INTO product VALUES (5,'Apple');
INSERT INTO product VALUES (6,'HTC');
INSERT INTO product VALUES (7,'Moto G3');
CREATE TABLE out_of_stock_product(
category_id number(3),
product_name VARCHAR(25)
);
INSERT INTO out_of_stock_product VALUES (1,'iPhone 6');
INSERT INTO out_of_stock_product VALUES (2,'Moto X Pure');
INSERT INTO out_of_stock_product VALUES (3,'Samsung Galaxy Note 5');
INSERT INTO out_of_stock_product VALUES (4,'LG G5');
INSERT INTO out_of_stock_product VALUES (5,'HTC 10');
INSERT INTO out_of_stock_product VALUES (6,'OnePlus 3');
INSERT INTO out_of_stock_product VALUES (7,'Samsung Galaxy S7');
INSERT INTO out_of_stock_product VALUES (8,'Nexus 5X');
INSERT INTO out_of_stock_product VALUES (9,'iPhone SE');
INSERT INTO out_of_stock_product VALUES (10,'iPhone 6S');
SQL> SELECT * FROM product;
Category ID | Product Name |
---|---|
1 | Nokia |
2 | Samsung |
3 | BlackBerry |
4 | Microsoft Lumia |
5 | Apple |
6 | HTC |
7 | Moto G3 |
SQL> SELECT * FROM out_of_stock_product;
Category ID | Product Name |
---|---|
1 | iPhone 6 |
2 | Moto X Pure |
3 | Samsung Galaxy Note 5 |
4 | LG G5 |
5 | HTC 10 |
6 | OnePlus 3 |
7 | Samsung Galaxy S7 |
8 | Nexus 5X |
9 | iPhone SE |
10 | iPhone 6S |
SQL> SELECT product_name FROM product
UNION
SELECT product_name FROM out_of_stock_product;
PRODUCT_NAME
-------------------------
iPhone 6S
iPhone SE
Nexus 5X
Samsung Galaxy S7
OnePlus 3
HTC 10
LG G5
Samsung Galaxy Note 5
Nokia
Moto X Pure
Samsung
iPhone 6
12 rows selected.
S.NO | Product Name |
---|---|
1 | iPhone 6S |
2 | iPhone SE |
3 | Nexus 5X |
4 | Samsung Galaxy S7 |
5 | OnePlus 3 |
6 | HTC 10 |
7 | LG G5 |
8 | Samsung Galaxy Note 5 |
9 | Nokia |
10 | Moto X Pure |
11 | Samsung |
12 | iPhone 6 |
SQL> SELECT product_name FROM product
UNION ALL
SELECT product_name FROM out_of_stock_product;
PRODUCT_NAME
-------------------------
iPhone 6S
iPhone SE
Nexus 5X
Samsung Galaxy S7
OnePlus 3
HTC 10
LG G5
Samsung Galaxy Note 5
Nokia
Moto X Pure
Samsung
iPhone 6
12 rows selected.
S.NO | Product Name |
---|---|
1 | iPhone 6S |
2 | iPhone SE |
3 | Nexus 5X |
4 | Samsung Galaxy S7 |
5 | OnePlus 3 |
6 | HTC 10 |
7 | LG G5 |
8 | Samsung Galaxy Note 5 |
9 | Nokia |
10 | Moto X Pure |
11 | Samsung |
12 | iPhone 6 |
13 | Nokia |
14 | Nexus 5X |
15 | Samsung |
16 | Moto X Pure |
17 | iPhone SE |