29.Union, Order By

Union, union all, Intersect

Imp points to remember:

1. Number of columns selected in all querys should be mapped
2. 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

Share this post: on Twitter on Facebook on Google+