Understanding Set Operators in Oracle SQL/PLSQL-Part01

Below we have tried to explain the use of Set operators in SQL by using simple examples.

First let’s check the Union all and Union operators. For that lets create tables as below:

SQL> desc Table_A

 Name                            Null?    Type

 ------------------------------- -------- ----

 ORDERNO                                  NUMBER(8)

 CUSTNO                                   NUMBER(5)

 CUSADD                                   VARCHAR2(10)

 CUSNAME                                  VARCHAR2(15)

SQL> desc Table_B

 Name                            Null?    Type

 ------------------------------- -------- ----

 ORDNO                                    NUMBER(8)

 ITEMNO                                   NUMBER(4)

 ITEMNAME                                 VARCHAR2(10)

 QTY                                      NUMBER(10)

Insert values as below:

SQL> select * from Table_A;

  ORDERNO    CUSTNO CUSADD     CUSNAME

--------- --------- ---------- ---------------

       12         1 mulund     mich

       13         2 mulund1    michl

       14         3 mulund3    michle

       15         4 mulund4    michlen

SQL> select * from Table_B;

    ORDNO    ITEMNO ITEMNAME         QTY

--------- --------- ---------- ---------

       12        67 nut              100

       13        77 nut1             200

       14        87 nut2             300

       15        97 nut3             400

       16       107 nut4             500

Note:The union operator DOES NOT return duplicate values.  The union all operator returns including the duplicate values.

SQL> select orderno from Table_A

union

select ordno from Table_B;

  ORDERNO

---------

       12

       13

       14

       15

       16

SQL> select orderno from Table_A

union all

select ordno from Table_B;

  ORDERNO

---------

       12

       13

       14

       15

       12

       13

       14

       15

       16

9 rows selected.