Union, union all, Intersect
Imp points to remember:
- Number of columns selected in all querys should be mapped
- Data type of columns should be mapped
Union: It merges the output of 2 or more select query, and shows only unique data.
SQL> select * from supplier;
VENDOR_ID VENDOR_NAME
101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
106 IBM
107 Samsung
110 Htc
8 rows selected.
SQL> select * from supplier_2015;
VENDOR_ID VENDOR_NAME
104 lenovo
105 philips
106 IBM
98 videocon
112 xolo
SQL> select vendor_id, vendor_name from supplier
union
select vendor_id, vendor_name from supplier_2015;
VENDOR_ID VENDOR_NAME
98 videocon
101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
105 philips
106 IBM
107 Samsung
110 Htc
112 xolo
Union all: Merges the output of 2 or more select querys, it shows all records from both querys
SQL> select vendor_id, vendor_name from supplier
2 union all
3 select vendor_id, vendor_name from supplier_2015;
VENDOR_ID VENDOR_NAME
101 dell
102 hcl
103 hp
104 lenovo
105 nokiya
106 IBM
107 Samsung
110 Htc
104 lenovo
105 philips
106 IBM
98 videocon
112 xolo
13 rows selected.
In the above example alls records from both (incuding duplicates) are displayed
Intersect : Shows common data from selected tables
SQL> select vendor_id, vendor_name from supplier
2 intersect
3 select vendor_id, vendor_name from supplier_2015;
VENDOR_ID VENDOR_NAME
104 lenovo
106 IBM